您现在的位置是：首页 > 博客网站制作 > CSS3|Html5CSS3|Html5

postgresql 删除重复数据的几种方法小结

杨青青2022-02-20【CSS3|Html5】人已围观

简介在使用PG数据库的这段时间，总结了三种删除重复数据的方法，其中最容易想到的就是最常规的删除方法，但此方法性能较差，删数据耗时较久，虽容易实现，但性能太差，影响写数据的速率。另外

在使用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行记录)