postgresql找到表中重复数据的行并删除
创建测试表并插入数据
1
|
create table aaa(id bigserial,col1 varchar (255)); |
1
|
insert into aaa values (1, 'b' ),(2, 'a' ),(3, 'b' ),(4, 'c' ); |
1
|
select * from aaa; |
找到重复行并删除
方法1:ctid表示数据行在它所处的表内的物理位置,ctid由两个数字组成,第一个数字表示物理块号,第二个数字表示在物理块中的行号。
1
|
select * from aaa where ctid not in ( select max (ctid) from aaa group by col1); |
删除重复行
1
|
delete from aaa where ctid not in ( select max (ctid) from aaa group by col1); |
方法2:利用exists
找到重复行
1
|
select * from aaa t1 where exists ( select 1 from aaa t2 where t1.col1=t2.col1 and t1.id<t2.id ) ----exists后的意思是同一列相等,但是自增id不相等且id小的那一个 |
删除重复行
1
|
delete from aaa t1 where exists ( select 1 from aaa t2 where t1.col1=t2.col1 and t1.id<t2.id ) |
postgresql常用的删除重复数据方法
最高效方法
测试环境验证,6600万行大表,删除2200万重复数据仅需3分钟
1
|
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)); |
PG中三种删除重复数据方法
首先创建一张基础表,并插入一定量的重复数据。
1
2
3
4
5
|
create table deltest(id int , name varchar (255)); create table deltest_bk ( like deltest); insert into deltest select generate_series(1, 10000), 'ZhangSan' ; insert into deltest select generate_series(1, 10000), 'ZhangSan' ; insert into deltest_bk select * from deltest; |
1. 常规删除方法
最容易想到的方法就是判断数据是否重复,对于重复的数据只保留ctid最小(或最大)的数据,删除其他的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
explain analyse delete from deltest a where a.ctid <> ( select min (t.ctid) from deltest t where a.id=t.id); ------------------------------------------------------------------------------------------- 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 select count (*) from deltest; count ------- 10000 |
可以看到,id相同的数据,保留ctid最小的,其他的删除。相当于把deltest表中的数据删掉一半,耗时达到67s多。相当慢。
2. group by删除方法
group by方法通过分组找到ctid最小的数据,然后删除其他数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
explain analyse delete from deltest a where a.ctid not in ( select min (ctid) from deltest group by id); ------------------------------------------------------------------------------------------- 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 select count (*) from deltest; count ------- 10000 |
可以看到同样是删除一半的数据,使用group by的方式,时间节省了一半。但仍含需要30s,下面试一下第三种删除操作。
3. 高效删除方法
1
2
3
4
5
6
7
8
9
|
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)); ----------------------------------------------------------------------------------------- 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)−>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>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.idSortMethod: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)−>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>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.idSortMethod: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)) Total runtime: 98.912 ms select count (*) from deltest; count ------- 10000 |
可以看到,居然只要98ms
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/weixin_44847119/article/details/120289882