postgresql - 删除重复记录(有效)

Question

PostgreSQL 10.4

我有一张 table :

Column   | Type
-------------------------
 id      | integer| 
 title   | character varying(200)
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)
    "phrases_index" btree (title)

内容如下:

rinopt=# select count(distinct title) from phrases;
  count  
---------
 9787866
(1 строка)

rinopt=# select count(title) from phrases;
  count   
----------
 13573099
(1 строка)

我只想保留不同的记录:

delete from phrases where phrases.id not in (
    select id from (
        select distinct on (title) * from phrases
    ) as phrases_id
)

好吧，这个命令已经运行了 16 个小时，我无法预测它何时结束。

将近 1400 万条记录不是一个小数据库，但并非不可想象。看来我做了一个很低效的select语句。

你能告诉我是否可以编写一个更优化的命令来清除重复项吗？

Answer 1

一个子选择就足够了。您可能会删除具有相同标题且 ID 大于当前 ID 的短语:

DELETE FROM phrases p WHERE EXISTS (
    SELECT p1.id FROM phrases p2 WHERE p.title = p2.title AND p.id > p2.id
);

类似 JOIN 的删除也是可能的:

DELETE FROM phrases p USING phrases p2 WHERE p.title = p2.title AND p.id > p2.id;

这两个语句都应保留每个标题的 id 最少的短语。