Subquery Scan

Description

If the rule-based optimizer (RBO) does not optimize a statement that contains a subquery and is being executed, this operator executes the query plan tree of the subquery and then transfers the subquery result to the upper-layer query.

Typical Scenarios

If a statement contains a subquery, the subquery scan operator is generated to obtain tuples from the subquery.

Examples

-- Prepare data.
gaussdb=#CREATE TABLE t_distinct(a int, b int, c int, d int, e regproc);

-- Execution result.
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)

-- Drop the table.
gaussdb=#DROP TABLE t_distinct;

In the preceding example, the output of the subquery scan operator is as follows.