postgresql 常用的删除重复数据方法

一、 最高效方法

测试环境验证，6600万行大表，删除2200万重复数据仅需3分钟

delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));

 

-- family_no 相同的数据，保留id 最小的，其他的删除。相当于把dcy_family表中的数据删掉一半
delete from dcy_family a 
where a.id = any(array (select id from (select row_number() over (partition by family_no), id from dcy_family) t where t.row_number > 1))

 

 

1. 常规删除方法

最容易想到的方法就是判断数据是否重复，对于重复的数据只保留ctid最小（或最大）的数据，删除其他的。

explain analyse delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id);

可以看到，id相同的数据，保留ctid最小的，其他的删除。相当于把deltest表中的数据删掉一半，耗时达到67s多。相当慢。

 

2. group by删除方法

group by方法通过分组找到ctid最小的数据，然后删除其他数据。

explain analyse delete from deltest a where a.ctid not in (select min(ctid) from deltest group by id);

可以看到同样是删除一半的数据，使用group by的方式，时间节省了一半。但仍含需要30s，下面试一下第三种删除操作。

 

3. 高效删除方法

explain analyze delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));

可以看到，只要98ms