PostgreSQL difference between filter and join filter

概述1. filter: 普通的过滤条件，where ...; having ...    join filter: 多表联结的时候，表与表之间联结时候的过滤条件    区分内联结和外联结来说明区别。 2. 建表，插入测试数据 postgres=# create table tb13(id integer,info character varying);CREATE TABLEpostgres=# 1. filter: 普通的过滤条件，where ...; having ...
join filter: 多表联结的时候，表与表之间联结时候的过滤条件

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

2. 建表，插入测试数据

@H_301_13@postgres=# create table tb13(ID integer,info character varying);CREATE tablepostgres=# create table tb14(ID integer,info character varying);CREATE tablepostgres=# postgres=# insert into tb13 values(1,'tb13');INSERT 0 1postgres=# insert into tb14 values(1,'tb14');INSERT 0 1 3. outer join：filter和join filter有区别。
①、使用join filter
@H_301_13@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来说没有起作用。
查看执行计划：
@H_301_13@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 >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
@H_301_13@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'已经起作用。
查看执行计划：
@H_301_13@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 >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
@H_301_13@postgres=# select * from tb13 inner join tb14 on tb13.ID=tb14.ID and tb13.info='no'; ID | info | ID | info ----+------+----+------(0 rows)查看执行计划：
@H_301_13@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 >Filter: ((info)::text = 'no'::text)</span> Rows Removed by Filter: 1 Total runtime: 0.062 ms(9 rows) ②、使用 filter
@H_301_13@postgres=# select * from tb13 inner join tb14 on tb13.ID=tb14.ID where tb13.info='no'; ID | info | ID | info ----+------+----+------(0 rows)查看执行计划：
@H_301_13@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 >Filter: ((info)::text = 'no'::text)</span> Rows Removed by Filter: 1 Total runtime: 0.036 ms(9 rows)

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

总结

以上是内存溢出为你收集整理的PostgreSQL difference between filter and join filter全部内容，希望文章能够帮你解决PostgreSQL difference between filter and join filter所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。