SubQuery Scan

算子说明

当执行一个包含子查询的语句时，如果优化器RBO没有对它进行优化，它会先执行子查询的查询计划树，然后将子查询的结果传递给上层查询。

典型场景

当语句中包含子查询的时候，会生成SubQuery Scan算子从子查询中获取元组。

示例

--数据准备。 
gaussdb=# CREATE TABLE t_distinct(a int, b int, c int, d int, e regproc);

--执行结果。 
gaussdb=# EXPLAIN (costs off, nodes off) SELECT COUNT(DISTINCT(a)), COUNT(DISTINCT(b)), d FROM t_distinct GROUP BY c, d;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Streaming (type: GATHER)
   ->  Hash Join
         Hash Cond: ((public.t_distinct.c = subquery."?column?") AND (public.t_distinct.d = subquery.d))
         ->  GroupAggregate
               Group By Key: public.t_distinct.c, public.t_distinct.d
               ->  Sort
                     Sort Key: public.t_distinct.c, public.t_distinct.d
                     ->  Streaming(type: REDISTRIBUTE)
                           ->  Seq Scan on t_distinct
         ->  Hash
               ->  Subquery Scan on subquery
                     ->  HashAggregate
                           Group By Key: public.t_distinct.c, public.t_distinct.d
                           ->  Streaming(type: REDISTRIBUTE)
                                 ->  HashAggregate
                                       Group By Key: public.t_distinct.c, public.t_distinct.d, public.t_distinct.a
                                       ->  Seq Scan on t_distinct
(17 rows)

--执行结果。
gaussdb=# EXPLAIN SELECT c1 FROM t1 tablesample system(10); 
                         QUERY PLAN
-------------------------------------------------------------
 Streaming (type: GATHER)  (cost=0.06..4.11 rows=2 width=32)
   Node/s: All datanodes
   ->  Sample Scan on t1  (cost=0.00..4.02 rows=2 width=32)
         Sampling: system (10::real)
(4 rows)

--删除表。
gaussdb=# DROP TABLE t_distinct;