PostgreSQL difference between filter and join filter
来源:互联网 发布:0基础学美工 编辑:程序博客网 时间:2024/05/02 06:44
1. filter: 普通的过滤条件,where ...; having ...
join filter: 多表联结的时候,表与表之间联结时候的过滤条件
区分内联结和外联结来说明区别。
①、使用join filter
查看执行计划:
查看执行计划:
①、使用 join filter
join filter: 多表联结的时候,表与表之间联结时候的过滤条件
区分内联结和外联结来说明区别。
2. 建表,插入测试数据
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 13. 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来操作的。
0 0
- PostgreSQL difference between filter and join filter
- what is the difference between inner join and outer join
- Difference between JOIN and JOIN FETCH in JPA
- The difference between (()) and ().
- difference between "<" and "<<"
- In SQL, what is the difference between a left join and a left outer join?
- In SQL, what’s the difference between a full join and an inner join?
- What is the difference between a left outer join and a right outer join?
- What is the difference between left join and left outer join?
- Magento: Join, filter, select and sort attributes, fields and tables
- Difference Between typedef and #define
- Difference between PCDATA and CDATA
- Difference between CComPtr and CComQIPtr
- Difference between RVCT and GCCE
- Difference between RVCT and GCCE
- difference between slist and list.
- difference between stack and heap
- Difference between replace and translate
- 理解变量在内存的存储原理
- 编译test代码
- 位图索引
- Methods
- Xcode中SVN不能提交.a文件的解决方法
- PostgreSQL difference between filter and join filter
- Flex4 权威指南源代码下载
- ActiveAndroid (二):如何进行增删改查
- Linux下配置环境变量
- 面试珠玑 硬链接与软连接的区别
- chromium CSS浅析(一) 默认htmlCss的形成逻辑(chromium39)
- 主流数据库Oracle Mysql DB2 电子版下载-77本
- 更换San交换机识别盘阵问题
- 面试珠玑 C语言sizeof求结构体的大小