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

数据库 2024/11/5 佚名

3 2 1

在使用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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

postgresql,删除重复数据

标签：

postgresql,删除重复数据

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

白云城资源网 Copyright www.dyhadc.com

评论“postgresql 删除重复数据的几种方法小结”

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

暂无“postgresql 删除重复数据的几种方法小结”评论...

www.dyhadc.com 白云城资源网

129,905影音资源

244,626技术资源

111,817软件资源

578,645站长资源

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

常规删除方法

group by删除方法

新的删除方法

postgresql,删除重复数据

Postgresql 数据库权限功能的使用总结

Postgresql 查看SQL语句执行效率的操作

评论“postgresql 删除重复数据的几种方法小结”

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

更新日志

友情链接