PostgreSQL 数据去重大法

阅读原文请点击：http://click.aliyun.com/m/22549/
摘要： 标签 PostgreSQL , 去重 , 单列去重 , 多列去重 , 行去重 , 多列混合去重 , varidict 参数 , 数组排序 , 数组元素重排 背景 去重的需求比较常见，去重也可以衍生出很多变种。

标签

PostgreSQL , 去重 , 单列去重 , 多列去重 , 行去重 , 多列混合去重 , varidict 参数 , 数组排序 , 数组元素重排

背景

去重的需求比较常见，去重也可以衍生出很多变种。例如

1. 单列去重，很好理解，就是按某列去除重复记录。保留规则（例如保留最新的，保留最旧的，或者保留某个其他字段最大的）。

2. 多列去重，按多列，去除重复记录。保留规则（例如保留最新的，保留最旧的，或者保留某个其他字段最大的）。

3. 行去重，按行，去除重复记录。保留规则（例如保留最新的，保留最旧的，或者保留某个其他字段最大的）。

4. 多列混合去重，按多列混合模式(ROW1: col1=1 , col2=2；ROW2: col1=2, col2=1；这种混合重复的去重)，去除重复记录。保留规则（例如保留最新的，保留最旧的，或者保留某个其他字段最大的）。

下面依次举例，使用不同的方法去重，用户可以自由选择效率最佳的。

单列去重

测试数据

create table test1(id int primary key, c1 int, c2 timestamp); 
insert into test1 select generate_series(1,1000000), random()*1000, clock_timestamp(); 
 
create index idx_test1 on test1(c1,id);   
-- 这个索引可以起到加速效果。如果没有这个索引，以下三种方法，第二种效率最高，其次是第三种。 
需求：去除c1重复的行，保留id最大的。

方法1，使用聚合，not in

postgres=# explain delete from test1 where id not in (select max(id) from test1 group by c1); 
                                                    QUERY PLAN                                                     
------------------------------------------------------------------------------------------------------------------ 
Delete on test1  (cost=35115.63..53023.01 rows=500055 width=6) 
   ->  Seq Scan on test1  (cost=35115.63..53023.01 rows=500055 width=6) 
         Filter: (NOT (hashed SubPlan 1)) 
         SubPlan 1 
           ->  GroupAggregate  (cost=0.42..35113.13 rows=1001 width=8) 
                 Group Key: test1_1.c1 
                 ->  Index Only Scan using idx_test1 on test1 test1_1  (cost=0.42..30102.57 rows=1000110 width=8) 
(7 rows) 
Time: 0.564 ms 
 
postgres=# delete from test1 where id not in (select max(id) from test1 group by c1); 
DELETE 998999 
Time: 1126.504 ms (00:01.127) 
方法2，使用窗口查询，IN

postgres=# explain select id from (select row_number() over(partition by c1 order by id) as rn, id from test1) t where t.rn<>1; 
                                            QUERY PLAN                                             
-------------------------------------------------------------------------------------------------- 
Subquery Scan on t  (cost=0.42..60075.54 rows=995109 width=4) 
   Filter: (t.rn <> 1) 
   ->  WindowAgg  (cost=0.42..47574.17 rows=1000110 width=16) 
         ->  Index Only Scan using idx_test1 on test1  (cost=0.42..30072.24 rows=1000110 width=8) 
(4 rows) 
Time: 0.512 ms 
 
postgres=# delete from test1 where id in (select id from (select row_number() over(partition by c1 order by id) as rn, id from test1) t where t.rn<>1); 
DELETE 998999 
Time: 2430.276 ms (00:02.430) 
方法3，使用PLPGSQL，内部使用排序+游标。

每条记录判断一次的方法去重，只有一次排序+每条记录比对的开销。

do language plpgsql $$      
declare 
  v_rec record; 
  v_c1 int; 
  cur1 cursor for select c1,id from test1 order by c1,id for update; 
begin 
  for v_rec in cur1 loop 
    if v_rec.c1 = v_c1 then 
      delete from test1 where current of cur1; 
    end if; 
    v_c1 := v_rec.c1; 
  end loop; 
end; 
$$; 
 
DO 
Time: 7345.773 ms (00:07.346) 
 
postgres=# select count(*) from test1; 
count  
------- 
  1001 
(1 row) 
 
Time: 61.672 ms 
postgres=# select * from test1 limit 10; 
id | c1  |             c2              
----+-----+---------------------------- 
  1 | 582 | 2017-06-02 10:21:10.60918 
  2 | 278 | 2017-06-02 10:21:10.609331 
  3 | 659 | 2017-06-02 10:21:10.609338 
  4 | 372 | 2017-06-02 10:21:10.609341 
  5 | 184 | 2017-06-02 10:21:10.609343 
  6 | 121 | 2017-06-02 10:21:10.609345 
  7 | 132 | 2017-06-02 10:21:10.609347 
  8 | 290 | 2017-06-02 10:21:10.609348 
  9 | 980 | 2017-06-02 10:21:10.60935 
10 | 305 | 2017-06-02 10:21:10.609352 
(10 rows) 
PostgreSQL 10黑科技

即使只有部分驱动列，也能使用索引排序。

例如index(c1)，可以用于order by c1,id;

《PostgreSQL 10.0 preview 优化器改进 - 不完整索引支持复合排序》

多列去重

测试数据

create table test1(id int primary key, c1 int, c2 int, c3 timestamp); 
insert into test1 select generate_series(1,1000000), random()*1000, random()*1000, clock_timestamp(); 
 
create index idx_test1 on test1(c1,c2,id);   
-- 这个索引可以起到加速效果。 
需求：去除c1,c2重复的行，保留id最大的。

方法1，

postgres=# explain (analyze,verbose,timing,costs,buffers) delete from test1 where id not in (select max(id) from test1 group by c1,c2); 
                                                                                 QUERY PLAN                                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Delete on public.test1  (cost=40820.36..59690.36 rows=500000 width=6) (actual time=1634.960..1634.960 rows=0 loops=1) 
   Buffers: shared hit=1378788 
   ->  Seq Scan on public.test1  (cost=40820.36..59690.36 rows=500000 width=6) (actual time=1090.956..1446.374 rows=367618 loops=1) 
         Output: test1.ctid 
         Filter: (NOT (hashed SubPlan 1)) 
         Rows Removed by Filter: 632382 
         Buffers: shared hit=1011170 
         SubPlan 1 
           ->  GroupAggregate  (cost=0.42..40570.36 rows=100000 width=12) (actual time=0.035..842.497 rows=632382 loops=1) 
                 Output: max(test1_1.id), test1_1.c1, test1_1.c2 
                 Group Key: test1_1.c1, test1_1.c2 
                 Buffers: shared hit=1004800 
                 ->  Index Only Scan using idx_test1 on public.test1 test1_1  (cost=0.42..32070.36 rows=1000000 width=12) (actual time=0.027..587.506 rows=1000000 loops=1) 
                       Output: test1_1.c1, test1_1.c2, test1_1.id 
                       Heap Fetches: 1000000 
                       Buffers: shared hit=1004800 
Planning time: 0.211 ms 
Execution time: 1641.679 ms 
(18 rows) 
方法2，
阅读原文请点击：http://click.aliyun.com/m/22549/