Postgresql使用left join 优化 not in

6560阅读 0评论2019-04-09 轨迹16
分类:Mysql/postgreSQL

Postgresql使用left join 优化 not in


场景:
查看在t1表中的数据,而这些数据不能出现在t2表。初学者容易想到 not in。这个问题可以使用left join 解决,如果两个表的结构一致,也可以采用差集来解决


使用例子

  1. 建立测试数据
    新建两个表t1, t2, 每个表有100万的数据。有999990是相同的。现在要找到在t1的数据,而不再t2的数据。

    create table t1(id int, info text);
    create table t2(id int, info text);
    
    insert into t1(id, info) select i, md5(i::text) from generate_series(1, 1000000) t(i);
    insert into t2(id, info) select i, md5(i::text) from generate_series(11, 1000000 + 10) t(i);
    
    create index on t1(id);
    create index on t2(id);
    
    postgres=# select count(*) from t1;
      count  
    ---------
     1000000
    (1 row)
    
    postgres=# select count(*) from t2;
      count  
    ---------
     1000000
    (1 row) 
  2. 使用not in 效果

    postgres=# explain  select * from t1 where id not in (select id from t2);
                                   QUERY PLAN                                   
    --------------------------------------------------------------------------------
     Gather  (cost=1000.00..6196106209.00 rows=500000 width=37)
       Workers Planned: 2
       ->  Parallel Seq Scan on t1  (cost=0.00..6196055209.00 rows=208333 width=37)
             Filter: (NOT (SubPlan 1))
             SubPlan 1
               ->  Materialize  (cost=0.00..27241.00 rows=1000000 width=4)
                     ->  Seq Scan on t2  (cost=0.00..18334.00 rows=1000000 width=4)
    (7 rows) 
  3. 使用left join 的效果

    postgres=# explain  select t1.* from t1 left join t2 on t1.id = t2.id where t2.* is  null;
                                      QUERY PLAN                                       
    ---------------------------------------------------------------------------------------
     Gather  (cost=23592.00..51286.28 rows=5000 width=37)
       Workers Planned: 2
       ->  Parallel Hash Left Join  (cost=22592.00..49786.28 rows=2083 width=37)
             Hash Cond: (t1.id = t2.id)
             Filter: (t2.* IS NULL)
             ->  Parallel Seq Scan on t1  (cost=0.00..12500.67 rows=416667 width=37)
             ->  Parallel Hash  (cost=12500.67..12500.67 rows=416667 width=65)
                   ->  Parallel Seq Scan on t2  (cost=0.00..12500.67 rows=416667 width=65)
    (8 rows) 

对比结果
6196106209 / 51286 = 120814
基本上相差12万倍

上一篇:PostgreSQL 在没有备份的情况下_如何恢复误删的数据
下一篇:Postgresql查询成本计算初探