Nested Loop¶
名称¶
嵌套循环连接算子
介绍¶
通过循环嵌套的方式获取关联数据，扫描时以外(左)表作为驱动表，先扫描驱动表，用读取的每一行驱动内表，当内(右)表的数据能够与之匹配时，进行关联
适合内表的关联列发生在唯一键值列或者主键列上的情况
举例¶
--清理环境
DROP TABLE t1 CASCADE;
DROP TABLE t2 CASCADE;
--创建环境
CREATE TABLE t1(id int, a int);
INSERT INTO t1 SELECT generate_series(1,10),random();
ANALYZE t1;
CREATE TABLE t2(id int,b int);
INSERT INTO t2 SELECT generate_series(1,10),random();
ANALYZE t2;
EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN t2 ON TRUE WHERE t1.a!=t2.b;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Nested Loop(Inner Join) (cost=7.11..16.46 rows=90 width=16) (actual time=0.07..0.11 rows=100 loops=1)
Join Filter: ("outer".A <> "inner".B)
-> Seq Scan on T1 (cost=0.00..7.10 rows=10 width=8) (actual time=0.02..0.02 rows=10 loops=1)
-> Materialize (cost=7.11..7.21 rows=10 width=8) (actual time=0.00..0.01 rows=10 loops=10)
-> Seq Scan on T2 (cost=0.00..7.10 rows=10 width=8) (actual time=0.01..0.02 rows=10 loops=1)
Planning Time: 0.29 msec
Execution Time: 0.20 msec
(7 rows)
EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN t2 ON TRUE WHERE RANDOM()>1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Nested Loop(Left Join) (cost=7.11..16.71 rows=33 width=16) (actual time=0.04..0.10 rows=53 loops=1)
Filter: (RANDOM() > 1::double precision)
-> Seq Scan on T1 (cost=0.00..7.10 rows=10 width=8) (actual time=0.01..0.02 rows=10 loops=1)
-> Materialize (cost=7.11..7.21 rows=10 width=8) (actual time=0.00..0.00 rows=10 loops=10)
-> Seq Scan on T2 (cost=0.00..7.10 rows=10 width=8) (actual time=0.01..0.02 rows=10 loops=1)
Planning Time: 0.50 msec
Execution Time: 0.56 msec
(7 rows)
EXPLAIN ANALYZE SELECT * FROM t1 left JOIN t2 ON t2.id>100 WHERE a IN (SELECT b FROM t1) AND RANDOM()=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop(Left Semi Join) (cost=7.13..21.56 rows=1 width=16) (actual time=0.05..0.05 rows=0 loops=1)
Startup Filter: (RANDOM() = 1::double precision)
-> Nested Loop(Left Join) (cost=7.13..14.45 rows=10 width=16) (actual time=0.05..0.05 rows=0 loops=1)
Filter: ("outer".A = "inner".B)
-> Seq Scan on T1 (cost=0.00..7.10 rows=10 width=8) (actual time=0.02..0.03 rows=10 loops=1)
-> Materialize (cost=7.13..7.14 rows=1 width=8) (actual time=0.00..0.00 rows=0 loops=10)
-> Seq Scan on T2 (cost=0.00..7.13 rows=1 width=8) (actual time=0.01..0.01 rows=0 loops=1)
Scan Key: (ID > 100)
-> RowcountMaterial (cost=7.13..7.13 rows=10 width=0) (never executed)
-> Seq Scan on T1 (cost=0.00..7.10 rows=10 width=0) (never executed)
Planning Time: 0.58 msec
Execution Time: 0.19 msec
(12 rows)
EXPLAIN ANALYZE SELECT * FROM t1 left JOIN t2 ON t2.id>100 WHERE a NOT IN (SELECT b FROM t1) AND RANDOM()=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop(Left Anti Semi Join) (cost=7.13..21.54 rows=10 width=16) (actual time=0.05..0.07 rows=10 loops=1)
Startup Anti Filter: ((("outer".A = "outer".B) OR ("outer".A IS NULL) OR ("outer".B IS NULL)) AND (RANDOM() = 1::double precision))
-> Nested Loop(Left Join) (cost=7.13..14.43 rows=10 width=16) (actual time=0.04..0.05 rows=10 loops=1)
-> Seq Scan on T1 (cost=0.00..7.10 rows=10 width=8) (actual time=0.02..0.03 rows=10 loops=1)
-> Materialize (cost=7.13..7.14 rows=1 width=8) (actual time=0.00..0.00 rows=0 loops=10)
-> Seq Scan on T2 (cost=0.00..7.13 rows=1 width=8) (actual time=0.02..0.02 rows=0 loops=1)
Scan Key: (ID > 100)
-> Seq Scan on T1 (cost=0.00..7.10 rows=10 width=0) (never executed)
Planning Time: 0.60 msec
Execution Time: 0.19 msec
(10 rows)
- Inner Join 算子属性，表示 连接类型 。
- Join Filter 限定条件，连接限定条件
- Filter 限定条件，一些其他的限定
- Startup Filter 限定条件，只对外表进行限定
- Startup Anti Filter 限定条件, 只对外表进行限定