PostgreSQL difference between filter and join filter

1. filter: 普通的过滤条件，where ...; having ...

   join filter: 多表联结的时候，表与表之间联结时候的过滤条件

   区分内联结和外联结来说明区别。

2. 建表，插入测试数据

postgres=# create table tb13(id integer,info character varying);
CREATE TABLE
postgres=# create table tb14(id integer,info character varying);
CREATE TABLE
postgres=# 
postgres=# insert into tb13 values(1,'tb13');
INSERT 0 1
postgres=# insert into tb14 values(1,'tb14');
INSERT 0 1
           

3. outer join：filter和join filter有区别。

①、使用join filter

postgres=# select * from tb13 left join tb14 on tb13.id=tb14.id and tb13.info='no';
 id | info | id | info 
----+------+----+------
  1 | tb13 |    | 
(1 row)
           

可以看到过滤条件tb13.info='no'对表tb13来说没有起作用。

查看执行计划：

postgres=# explain analyze select * from tb13 left join tb14 on tb13.id=tb14.id and tb13.info='no';
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=170.85..309.37 rows=1230 width=72) (actual time=0.027..0.027 rows=1 loops=1)
   Merge Cond: (tb13.id = tb14.id)
   <span style="color:#ff0000;">Join Filter: ((tb13.info)::text = 'no'::text)</span>
   Rows Removed by Join Filter: 1
   ->  Sort  (cost=85.43..88.50 rows=1230 width=36) (actual time=0.014..0.014 rows=1 loops=1)
         Sort Key: tb13.id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on tb13  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.007..0.008 rows=1 loops=1)
   ->  Sort  (cost=85.43..88.50 rows=1230 width=36) (actual time=0.004..0.004 rows=1 loops=1)
         Sort Key: tb14.id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on tb14  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.067 ms
(13 rows)
           

②、使用filter

postgres=# select * from tb13 left join tb14 on tb13.id=tb14.id where tb13.info='no';
 id | info | id | info 
----+------+----+------
(0 rows)
           

可以看到过滤条件tb13.info='no'已经起作用。

查看执行计划：

postgres=# explain analyze select * from tb13 left join tb14 on tb13.id=tb14.id where tb13.info='no';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=25.45..52.73 rows=37 width=72) (actual time=5.707..5.707 rows=0 loops=1)
   Hash Cond: (tb14.id = tb13.id)
   ->  Seq Scan on tb14  (cost=0.00..22.30 rows=1230 width=36) (never executed)
   ->  Hash  (cost=25.38..25.38 rows=6 width=36) (actual time=0.017..0.017 rows=0 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 0kB
         ->  Seq Scan on tb13  (cost=0.00..25.38 rows=6 width=36) (actual time=0.017..0.017 rows=0 loops=1)
               <span style="color:#ff0000;">Filter: ((info)::text = 'no'::text)</span>
               Rows Removed by Filter: 1
 Total runtime: 34.031 ms
(9 rows)
           

4. inner join: filter和join filter没有区别。

①、使用 join filter

postgres=# select * from tb13 inner join tb14 on tb13.id=tb14.id and tb13.info='no';
 id | info | id | info 
----+------+----+------
(0 rows)
           

查看执行计划：

postgres=# explain analyze select * from tb13 inner join tb14 on tb13.id=tb14.id and tb13.info='no';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=25.45..52.73 rows=37 width=72) (actual time=0.034..0.034 rows=0 loops=1)
   Hash Cond: (tb14.id = tb13.id)
   ->  Seq Scan on tb14  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.006..0.006 rows=1 loops=1)
   ->  Hash  (cost=25.38..25.38 rows=6 width=36) (actual time=0.022..0.022 rows=0 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 0kB
         ->  Seq Scan on tb13  (cost=0.00..25.38 rows=6 width=36) (actual time=0.022..0.022 rows=0 loops=1)
               <span style="color:#ff0000;">Filter: ((info)::text = 'no'::text)</span>
               Rows Removed by Filter: 1
 Total runtime: 0.062 ms
(9 rows)
           

②、使用 filter

postgres=# select * from tb13 inner join tb14 on tb13.id=tb14.id where tb13.info='no';
 id | info | id | info 
----+------+----+------
(0 rows)
           

查看执行计划：

postgres=# explain analyze select * from tb13 inner join tb14 on tb13.id=tb14.id where tb13.info='no';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=25.45..52.73 rows=37 width=72) (actual time=0.015..0.015 rows=0 loops=1)
   Hash Cond: (tb14.id = tb13.id)
   ->  Seq Scan on tb14  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.006..0.006 rows=1 loops=1)
   ->  Hash  (cost=25.38..25.38 rows=6 width=36) (actual time=0.004..0.004 rows=0 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 0kB
         ->  Seq Scan on tb13  (cost=0.00..25.38 rows=6 width=36) (actual time=0.004..0.004 rows=0 loops=1)
               <span style="color:#ff0000;">Filter: ((info)::text = 'no'::text)</span>
               Rows Removed by Filter: 1
 Total runtime: 0.036 ms
(9 rows)
           

可以看到，过滤条件都起作用，在执行计划里面也可以看到在内联结中都是当作filter来操作的。