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) -- Execution result. 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) -- Drop the table. gaussdb=# DROP TABLE t_distinct;
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