postgresql 刪除重復數據的幾種方法小結
在使用PG數據庫的這段時間,總結瞭三種刪除重復數據的方法,其中最容易想到的就是最常規的刪除方法,但此方法性能較差,刪數據耗時較久,雖容易實現,但性能太差,影響寫數據的速率。
另外就是被使用的group by刪除方法,效率較高。
還有一種是剛發現的,還沒有驗證,現在就總結下這三種刪除方法,並驗證各自的執行效率。
首先創建一張基礎表,並插入一定量的重復數據。
test=# create table deltest(id int, name varchar(255)); CREATE TABLE test=# create table deltest_bk (like deltest); CREATE TABLE test=# insert into deltest select generate_series(1, 10000), 'ZhangSan'; INSERT 0 10000 test=# insert into deltest select generate_series(1, 10000), 'ZhangSan'; INSERT 0 10000 test=# insert into deltest_bk select * from deltest;
常規刪除方法
最容易想到的方法就是判斷數據是否重復,對於重復的數據隻保留ctid最小(或最大)的那條數據,刪除其他的數據。
test=# explain analyse delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Delete on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=67758.866..67758.866 rows=0 loops=1) -> Seq Scan on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=32896.517..67663.228 rows=10000 loops=1) Filter: (ctid <> (SubPlan 1)) Rows Removed by Filter: 10000 SubPlan 1 -> Aggregate (cost=128.10..128.10 rows=1 width=6) (actual time=3.374..3.374 rows=1 loops=20000) -> Seq Scan on deltest t (cost=0.00..128.07 rows=8 width=6) (actual time=0.831..3.344 rows=2 loops=20000) Filter: (a.id = id) Rows Removed by Filter: 19998 Total runtime: 67758.931 ms test=# select count(*) from deltest; count ------- 10000 (1 行記錄)
可以看到,id相同的數據,保留ctid最小的那條,其他的刪除。相當於把deltest表中的數據刪掉一半,耗時達到67s多。相當慢。
group by刪除方法
第二種方法為group by方法,通過分組找到ctid最小的數據,然後刪除其他數據。
test=# truncate table deltest; TRUNCATE TABLE test=# insert into deltest select * from deltest_bk; INSERT 0 20000 test=# explain analyse delete from deltest a where a.ctid not in (select min(ctid) from deltest group by id); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Delete on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=30942.496..30942.496 rows=0 loops=1) -> Seq Scan on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=10186.296..30814.366 rows=10000 loops=1) Filter: (NOT (SubPlan 1)) Rows Removed by Filter: 10000 SubPlan 1 -> Materialize (cost=131.89..134.89 rows=200 width=10) (actual time=0.001..0.471 rows=7500 loops=20000) -> HashAggregate (cost=131.89..133.89 rows=200 width=10) (actual time=10.568..13.584 rows=10000 loops=1) -> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.006..3.829 rows=20000 loops=1) Total runtime: 30942.819 ms (9 行記錄) test=# select count(*) from deltest; count ------- 10000 (1 行記錄)
可以看到同樣是刪除一半的數據,使用group by的方式,時間節省瞭一半。但仍含需要30s,下面試一下第三種刪除操作。
新的刪除方法
在postgres修煉之道這本書中,作者提到一種效率較高的刪除方法, 在這裡驗證一下,具體如下:
test=# truncate table deltest; TRUNCATE TABLE test=# insert into deltest select * from deltest_bk; INSERT 0 20000 test=# 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)); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Delete on deltest a (cost=250.74..270.84 rows=10 width=6) (actual time=98.363..98.363 rows=0 loops=1) InitPlan 1 (returns $0) -> Subquery Scan on t (cost=204.95..250.73 rows=509 width=6) (actual time=29.446..47.867 rows=10000 loops=1) Filter: (t.row_number > 1) Rows Removed by Filter: 10000 -> WindowAgg (cost=204.95..231.66 rows=1526 width=10) (actual time=29.436..44.790 rows=20000 loops=1) -> Sort (cost=204.95..208.77 rows=1526 width=10) (actual time=12.466..13.754 rows=20000 loops=1) Sort Key: deltest.id Sort Method: quicksort Memory: 1294kB -> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.021..5.110 rows=20000 loops=1) -> Tid Scan on deltest a (cost=0.01..20.11 rows=10 width=6) (actual time=82.983..88.751 rows=10000 loops=1) TID Cond: (ctid = ANY ($0)) Total runtime: 98.912 ms (13 行記錄) test=# select count(*) from deltest; count ------- 10000 (1 行記錄)
看到上述結果,真讓我吃驚瞭一把,這麼快的刪除方法還是首次看到,自己真實孤陋寡聞,在這裡要膜拜一下修煉之道這本書的大神作者瞭。
補充:pgsql 刪除表中重復數據保留其中的一條
1.在表中(表名:table 主鍵:id)增加一個字段rownum,類型為serial
2.執行語句:
delete from table where rownum not in( select max(rownum) from table group by id )
3.最後刪除rownum
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。
推薦閱讀:
- postgresql如何找到表中重復數據的行並刪除
- postgresql insert into select無法使用並行查詢的解決
- 解決postgresql insert into select無法使用並行查詢的問題
- PostgreSQL 對IN,EXISTS,ANY/ALL,JOIN的sql優化方案
- 淺談pg_hint_plan定制執行計劃