Subquery Scan

名称

子查询扫描算子

介绍

Subquery Scan算子的作用是以另一个査询计划树（子计划）为扫描对象进行元组的扫描，可用在子查询或者组合查询中

子查询主要包含如下几个关键字: EXISTS, IN, NOT IN, ANY/SOME, ALL等，在查询编译阶段往往会对IN、ANY和EXISTS的子查询优化为半连接形式(子连接提升)

组合查询UNION、INTERSECT、EXCEPT的语句中会使用Subquery Scan算子

举例

--清理环境
DROP TABLE t1 CASCADE;

--创建环境
CREATE TABLE t1(a int primary key, b int);
INSERT INTO t1 VALUES(1,12);
INSERT INTO t1 VALUES(2,13);
INSERT INTO t1 VALUES(3,14);
INSERT INTO t1 VALUES(4,14);
ANALYZE t1;

EXPLAIN ANALYZE SELECT * FROM t1 WHERE a<2 UNION SELECT * FROM t1 WHERE b=14;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 MergeAppend  (cost=11.21..11.24 rows=0 width=8) (actual time=0.07..0.07 rows=3 loops=1)
   ->  Subquery Scan "*SELECT* 1"  (cost=4.15..4.16 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=1)
        ->  Sort  (cost=4.15..4.16 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=1)
               Sort Key: A, B
               ->  Index Scan using T1_PKEY(Normal Index Scan) on T1  (cost=0.01..4.14 rows=1 width=8) (actual time=0.02..0.02 rows=1 loops=1)
                     Index Key: (A < 2)
   ->  Subquery Scan "*SELECT* 2"  (cost=7.06..7.06 rows=2 width=8) (actual time=0.03..0.04 rows=2 loops=1)
         ->  Sort  (cost=7.06..7.06 rows=2 width=8) (actual time=0.03..0.03 rows=2 loops=1)
               Sort Key: A, B
               ->  Seq Scan on T1  (cost=0.00..7.05 rows=2 width=8) (actual time=0.02..0.02 rows=2 loops=1)
                     Scan Key: (B = 14)
 Planning Time: 0.62 msec
 Execution Time: 0.31 msec
(13 rows)

--清理环境
DROP TABLE t2 CASCADE;

--创建环境
CREATE TABLE t2(a int);
INSERT INTO t2 VALUES(2);
INSERT INTO t2 VALUES(3);
ANALYZE t1;

--子查询被提升为半连接的情况
EXPLAIN ANALYZE SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Nested Loop(Left Semi Join)  (cost=0.00..106.95 rows=4 width=8) (actual time=0.05..0.07 rows=2 loops=1)
   Join Filter: ("outer".A = "inner".A)
   ->  Seq Scan on T1  (cost=0.00..7.04 rows=4 width=8) (actual time=0.02..0.02 rows=4 loops=1)
   ->  Seq Scan on T2  (cost=0.00..89.92 rows=8192 width=4) (actual time=0.01..0.01 rows=2 loops=4)
 Planning Time: 0.88 msec
 Execution Time: 0.17 msec
(6 rows)