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

Example: The query contains subqueries that cannot be pushed down.

-- Prepare data.
gaussdb=# CREATE TABLE t1(c1 number, c2 number, c3 number); 
CREATE TABLE 
gaussdb=# CREATE TABLE t2(c1 number, c2 number, c3 number); 
CREATE TABLE
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,10000), (random() * 10)::integer,(random() * 10)::integer );
INSERT 0 10000
gaussdb=# INSERT INTO t2 VALUES(generate_series(1,10000), (random() * 10)::integer,(random() * 10)::integer );
INSERT 0 10000

-- Execution result.
gaussdb=# EXPLAIN SELECT * FROM t1 WHERE c2 > (SELECT avg(c2) FROM t2 WHERE t2.c1 = t1.c1);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Hash Join  (cost=87.87..198.64 rows=886 width=96)
   Hash Cond: (t1.c1 = subquery."?column?")
   Join Filter: (t1.c2 > subquery.avg)
   ->  Seq Scan on t1  (cost=0.00..67.58 rows=2658 width=96)
   ->  Hash  (cost=85.37..85.37 rows=200 width=64)
         ->  Subquery Scan on subquery  (cost=80.87..85.37 rows=200 width=64)
               ->  HashAggregate  (cost=80.87..83.37 rows=200 width=64)
                     Group By Key: t2.c1
                     ->  Seq Scan on t2  (cost=0.00..67.58 rows=2658 width=64)
(9 rows)

-- Drop.
gaussdb=# DROP TABLE t1,t2;