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;
Item |
Description |
---|---|
Subquery Scan |
Operator name. |
Hash |
Operator for creating a hash table in an inner table. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot