postgresql 删除重复数据的几种方法小结
在使用PG数据库的这段时间，总结了三种删除重复数据的方法，其中最容易想到的就是最常规的删除方法，但此方法性能较差，删数据耗时较久，虽容易实现，但性能太差，影响写数据的速率。
另外就是被使用的groupby删除方法，效率较高。
还有一种是刚发现的，还没有验证，现在就总结下这三种删除方法，并验证各自的执行效率。
首先创建一张基础表，并插入一定量的重复数据。
test=#createtabledeltest(idint,namevarchar(255)); CREATETABLE test=#createtabledeltest_bk(likedeltest); CREATETABLE test=#insertintodeltestselectgenerate_series(1,10000),'ZhangSan'; INSERT010000 test=#insertintodeltestselectgenerate_series(1,10000),'ZhangSan'; INSERT010000 test=#insertintodeltest_bkselect*fromdeltest;
常规删除方法
最容易想到的方法就是判断数据是否重复，对于重复的数据只保留ctid最小（或最大）的那条数据，删除其他的数据。
test=#explainanalysedeletefromdeltestawherea.ctid<>(selectmin(t.ctid)fromdeltesttwherea.id=t.id); QUERYPLAN ----------------------------------------------------------------------------------------------------------------------------- Deleteondeltesta(cost=0.00..195616.30rows=1518width=6)(actualtime=67758.866..67758.866rows=0loops=1) ->SeqScanondeltesta(cost=0.00..195616.30rows=1518width=6)(actualtime=32896.517..67663.228rows=10000loops=1) Filter:(ctid<>(SubPlan1)) RowsRemovedbyFilter:10000 SubPlan1 ->Aggregate(cost=128.10..128.10rows=1width=6)(actualtime=3.374..3.374rows=1loops=20000) ->SeqScanondeltestt(cost=0.00..128.07rows=8width=6)(actualtime=0.831..3.344rows=2loops=20000) Filter:(a.id=id) RowsRemovedbyFilter:19998 Totalruntime:67758.931ms test=#selectcount(*)fromdeltest; count ------- 10000 (1行记录)
可以看到，id相同的数据，保留ctid最小的那条，其他的删除。相当于把deltest表中的数据删掉一半，耗时达到67s多。相当慢。
groupby删除方法
第二种方法为groupby方法，通过分组找到ctid最小的数据，然后删除其他数据。
test=#truncatetabledeltest; TRUNCATETABLE test=#insertintodeltestselect*fromdeltest_bk; INSERT020000 test=#explainanalysedeletefromdeltestawherea.ctidnotin(selectmin(ctid)fromdeltestgroupbyid); QUERYPLAN ---------------------------------------------------------------------------------------------------------------------------------- Deleteondeltesta(cost=131.89..2930.46rows=763width=6)(actualtime=30942.496..30942.496rows=0loops=1) ->SeqScanondeltesta(cost=131.89..2930.46rows=763width=6)(actualtime=10186.296..30814.366rows=10000loops=1) Filter:(NOT(SubPlan1)) RowsRemovedbyFilter:10000 SubPlan1 ->Materialize(cost=131.89..134.89rows=200width=10)(actualtime=0.001..0.471rows=7500loops=20000) ->HashAggregate(cost=131.89..133.89rows=200width=10)(actualtime=10.568..13.584rows=10000loops=1) ->SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.006..3.829rows=20000loops=1) Totalruntime:30942.819ms (9行记录) test=#selectcount(*)fromdeltest; count ------- 10000 (1行记录)
可以看到同样是删除一半的数据，使用groupby的方式，时间节省了一半。但仍含需要30s，下面试一下第三种删除操作。
新的删除方法
在postgres修炼之道这本书中，作者提到一种效率较高的删除方法，在这里验证一下，具体如下：
test=#truncatetabledeltest; TRUNCATETABLE test=#insertintodeltestselect*fromdeltest_bk; INSERT020000 test=#explainanalyzedeletefromdeltestawherea.ctid=any(array(selectctidfrom(selectrow_number()over(partitionbyid),ctidfromdeltest)twheret.row_number>1)); QUERYPLAN ---------------------------------------------------------------------------------------------------------------------------------- Deleteondeltesta(cost=250.74..270.84rows=10width=6)(actualtime=98.363..98.363rows=0loops=1) InitPlan1(returns$0) ->SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1) Filter:(t.row_number>1) RowsRemovedbyFilter:10000 ->WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1) ->Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1) SortKey:deltest.id SortMethod:quicksortMemory:1294kB ->SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1) ->TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1) TIDCond:(ctid=ANY($0)) Totalruntime:98.912ms (13行记录) test=#selectcount(*)fromdeltest; count ------- 10000 (1行记录)
看到上述结果，真让我吃惊了一把，这么快的删除方法还是首次看到，自己真实孤陋寡闻，在这里要膜拜一下修炼之道这本书的大神作者了。
补充：pgsql删除表中重复数据保留其中的一条
1.在表中(表名：table主键:id)增加一个字段rownum,类型为serial
2.执行语句：
deletefromtablewhererownumnotin( selectmax(rownum)fromtablegroupbyid )
3.最后删除rownum
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。