Postgres中去除重复项的最有效方法是什么？

Question

Postgres中去除重复项的最有效方法是什么？

8

我通常使用以下查询删除重复项：

delete from test a
using test b 
where a.ctid < b.ctid
and a.col1=b.col1
and a.col2=b.col2
and a.col3=b.col3

此外，我见过人们使用以下查询：

DELETE FROM test WHERE test.ctid NOT IN 
(SELECT ctid FROM (
    SELECT DISTINCT ON (col1, col2) *
  FROM test));

甚至这个（重复直到你用尽所有副本）：

delete from test ju where ju.ctid in 
(select ctid from (
select  distinct on (col1, col2) * from test ou
where (select count(*) from test inr
where inr.col1= ou.col1 and inr.col2=ou.col2) > 1

我现在遇到了一个有500万行的表，其中列中有索引与where子句匹配。我想知道：

所有看起来一样的方法中，哪一种最有效，为什么？我刚刚运行了第二种方法，花了45分钟以上才删除重复项。我只是好奇哪种方法最有效，以防我需要从另一个巨大的表中删除重复项。它是否有一个主键并不重要，你可以随时创建或不创建。

- A.T.

ctid 值的比较速度相当慢，您是否有其他唯一标识行的方法？也许可以使用 serial（或 identity）列？ - user330315

我可以随时创建一个串行ID，这没有问题。但是第二个示例运行了45分钟后被终止，而第一个示例仅在50秒后完成。我很确定它们都会删除重复项，只留下一行。既然两者都做同样的事情，为什么会有如此巨大的时间差异呢？ - A.T.

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- S-Man · Accepted Answer

演示：db<>fiddle

使用窗口函数row_number()可以轻松地找到重复项：

SELECT ctid 
FROM(
    SELECT 
        *, 
        ctid,
        row_number() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) 
    FROM test
)s
WHERE row_number >= 2

这个命令将绑定的行分组并添加行计数器。因此，每一行中 row_number > 1 的都是可以删除的重复行：

DELETE 
FROM test
WHERE ctid IN 
(
    SELECT ctid 
    FROM(
        SELECT 
            *, 
            ctid,
            row_number() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) 
        FROM test
    )s
    WHERE row_number >= 2
)

我不知道这个解决方案是否比你尝试的更快，但你可以试试。此外，正如 @a_horse_with_no_name 已经提到的，我建议出于性能问题使用自己的标识符，而不是 ctid 。

编辑：

对于我的测试数据，你的第一个版本似乎比我的解决方案快一点。你的第二个版本似乎更慢，你的第三个版本对我无效（在修复编译错误后它不显示结果）。

演示：db<>fiddle