Help Center/
GaussDB/
Distributed_8.x/
SQL Optimization/
Optimization Cases/
Using DN Gather to Reduce Stream Nodes in the Plan
Updated on 2024-06-03 GMT+08:00
Using DN Gather to Reduce Stream Nodes in the Plan
The DN Gather is used to remove the stream nodes from the distribution plan and send data to a node for calculation. This reduces the cost of data redistribution during the execution of the distribution plan and improves the single query efficiency and the overall throughput capability of the system. However, DN Gather is oriented to small-data-volume scenarios of TP. For small-data-volume queries, performance can be improved because the cost of data redistribution is reduced and the computing power of a single node is sufficient. Multi-node parallel computing is more advantageous for large-data-volume computing. You need to enable and disable DN Gather to determine which one is faster. (The default value of dngather_min_rows is 500. The following uses the default value.)
Preparing the Case Environment
To facilitate case demonstration, you need to prepare the following table creation statements:
-- Clean the environment. DROP SCHEMA IF EXISTS dn_gather_test CASCADE; CREATE SCHEMA dn_gather_test; SET current_schema=dn_gather_test; -- Create a test table. CREATE TABLE t1(a INT, b INT, c INT, d INT); CREATE TABLE t2(a INT, b INT, c INT, d INT); CREATE TABLE t3(a INT, b INT, c INT, d INT); CREATE TABLE t4(a INT, b INT, c INT, d INT);
Gather Join
To converge the join results to a single DN, the following conditions must be met:
- The number of data rows estimated by the optimizer before and after join is less than the threshold.
- The subnodes of join are all stream nodes.
For example, the subnodes of join are all stream nodes, and broadcast is disabled.
gaussdb=# set enable_broadcast=false; SET gaussdb=# set explain_perf_mode=pretty; SET gaussdb=# set enable_dngather=false; SET gaussdb=# explain select count(*) from t1, t2 where t1.b = t2.b; id | operation | E-rows | E-width | E-costs ----+--------------------------------------------------+--------+---------+--------- 1 | -> Aggregate | 1 | 8 | 31.46 2 | -> Streaming (type: GATHER) | 3 | 8 | 31.46 3 | -> Aggregate | 3 | 8 | 31.34 4 | -> Hash Join (5,7) | 30 | 0 | 31.30 5 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 6 | -> Seq Scan on t1 | 30 | 4 | 14.14 7 | -> Hash | 29 | 4 | 15.49 8 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 9 | -> Seq Scan on t2 | 30 | 4 | 14.14 (9 rows) Predicate Information (identified by plan id) ----------------------------------------------- 4 --Hash Join (5,7) Hash Cond: (t1.b = t2.b) (2 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select count(*) from t1, t2 where t1.b = t2.b; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 1 | 8 | 32.53 2 | -> Aggregate | 1 | 8 | 32.47 3 | -> Hash Join (4,6) | 30 | 0 | 32.38 4 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 15.69 5 | -> Seq Scan on t1 | 30 | 4 | 14.14 6 | -> Hash | 30 | 4 | 15.69 7 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 15.69 8 | -> Seq Scan on t2 | 30 | 4 | 14.14 (8 rows) Predicate Information (identified by plan id) ----------------------------------------------- 3 --Hash Join (4,6) Hash Cond: (t1.b = t2.b) (2 rows) gaussdb=# set enable_dngather=false; SET gaussdb=# explain select * from t1, t2, t3, t4 where t1.b = t2.b and t2.c = t3.c and t3.d = t4.d order by t1.a; id | operation | E-rows | E-width | E-costs ----+-----------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 144 | 66.46 2 | -> Sort | 30 | 144 | 65.05 3 | -> Hash Join (4,16) | 30 | 144 | 64.86 4 | -> Streaming(type: REDISTRIBUTE) | 30 | 108 | 49.05 5 | -> Hash Join (6,13) | 30 | 108 | 48.08 6 | -> Streaming(type: REDISTRIBUTE) | 30 | 72 | 32.27 7 | -> Hash Join (8,10) | 30 | 72 | 31.30 8 | -> Streaming(type: REDISTRIBUTE) | 30 | 36 | 15.49 9 | -> Seq Scan on t1 | 30 | 36 | 14.14 10 | -> Hash | 29 | 36 | 15.49 11 | -> Streaming(type: REDISTRIBUTE) | 30 | 36 | 15.49 12 | -> Seq Scan on t2 | 30 | 36 | 14.14 13 | -> Hash | 29 | 36 | 15.49 14 | -> Streaming(type: REDISTRIBUTE) | 30 | 36 | 15.49 15 | -> Seq Scan on t3 | 30 | 36 | 14.14 16 | -> Hash | 29 | 36 | 15.49 17 | -> Streaming(type: REDISTRIBUTE) | 30 | 36 | 15.49 18 | -> Seq Scan on t4 | 30 | 36 | 14.14 (18 rows) Predicate Information (identified by plan id) ----------------------------------------------- 3 --Hash Join (4,16) Hash Cond: (t3.d = t4.d) 5 --Hash Join (6,13) Hash Cond: (t2.c = t3.c) 7 --Hash Join (8,10) Hash Cond: (t1.b = t2.b) (6 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select * from t1, t2, t3, t4 where t1.b = t2.b and t2.c = t3.c and t3.d = t4.d order by t1.a; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 144 | 68.47 2 | -> Sort | 30 | 144 | 66.36 3 | -> Hash Join (4,10) | 30 | 144 | 65.55 4 | -> Hash Join (5,7) | 30 | 72 | 32.38 5 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode3) | 30 | 36 | 15.69 6 | -> Seq Scan on t1 | 30 | 36 | 14.14 7 | -> Hash | 30 | 36 | 15.69 8 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode3) | 30 | 36 | 15.69 9 | -> Seq Scan on t2 | 30 | 36 | 14.14 10 | -> Hash | 30 | 72 | 32.38 11 | -> Hash Join (12,14) | 30 | 72 | 32.38 12 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode3) | 30 | 36 | 15.69 13 | -> Seq Scan on t3 | 30 | 36 | 14.14 14 | -> Hash | 30 | 36 | 15.69 15 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode3) | 30 | 36 | 15.69 16 | -> Seq Scan on t4 | 30 | 36 | 14.14 (16 rows) Predicate Information (identified by plan id) ----------------------------------------------- 3 --Hash Join (4,10) Hash Cond: (t2.c = t3.c) 4 --Hash Join (5,7) Hash Cond: (t1.b = t2.b) 11 --Hash Join (12,14) Hash Cond: (t3.d = t4.d) (6 rows) gaussdb=# set enable_dngather=false; SET gaussdb=# explain select count(*) from t1, t2, t3, t4 where t1.b = t2.b and t2.c = t3.c and t3.d = t4.d group by t1.b order by t1.b; id | operation | E-rows | E-width | E-costs ----+--------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 12 | 66.45 2 | -> GroupAggregate | 30 | 12 | 65.20 3 | -> Sort | 30 | 4 | 65.05 4 | -> Hash Join (5,17) | 30 | 4 | 64.86 5 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 49.05 6 | -> Hash Join (7,14) | 30 | 4 | 48.08 7 | -> Streaming(type: REDISTRIBUTE) | 30 | 8 | 32.27 8 | -> Hash Join (9,11) | 30 | 8 | 31.30 9 | -> Streaming(type: REDISTRIBUTE) | 30 | 8 | 15.49 10 | -> Seq Scan on t2 | 30 | 8 | 14.14 11 | -> Hash | 29 | 8 | 15.49 12 | -> Streaming(type: REDISTRIBUTE) | 30 | 8 | 15.49 13 | -> Seq Scan on t3 | 30 | 8 | 14.14 14 | -> Hash | 29 | 4 | 15.49 15 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 16 | -> Seq Scan on t4 | 30 | 4 | 14.14 17 | -> Hash | 29 | 4 | 15.49 18 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 19 | -> Seq Scan on t1 | 30 | 4 | 14.14 (19 rows) Predicate Information (identified by plan id) ----------------------------------------------- 4 --Hash Join (5,17) Hash Cond: (t2.b = t1.b) 6 --Hash Join (7,14) Hash Cond: (t3.d = t4.d) 8 --Hash Join (9,11) Hash Cond: (t2.c = t3.c) (6 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select count(*) from t1, t2, t3, t4 where t1.b = t2.b and t2.c = t3.c and t3.d = t4.d group by t1.b order by t1.b; id | operation | E-rows | E-width | E-costs ----+----------------------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 12 | 68.69 2 | -> GroupAggregate | 30 | 12 | 66.81 3 | -> Sort | 30 | 4 | 66.36 4 | -> Hash Join (5,11) | 30 | 4 | 65.55 5 | -> Hash Join (6,8) | 30 | 8 | 32.38 6 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 15.69 7 | -> Seq Scan on t1 | 30 | 4 | 14.14 8 | -> Hash | 30 | 8 | 15.69 9 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 8 | 15.69 10 | -> Seq Scan on t2 | 30 | 8 | 14.14 11 | -> Hash | 30 | 4 | 32.38 12 | -> Hash Join (13,15) | 30 | 4 | 32.38 13 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 8 | 15.69 14 | -> Seq Scan on t3 | 30 | 8 | 14.14 15 | -> Hash | 30 | 4 | 15.69 16 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 15.69 17 | -> Seq Scan on t4 | 30 | 4 | 14.14 (17 rows) Predicate Information (identified by plan id) ----------------------------------------------- 4 --Hash Join (5,11) Hash Cond: (t2.c = t3.c) 5 --Hash Join (6,8) Hash Cond: (t1.b = t2.b) 12 --Hash Join (13,15) Hash Cond: (t3.d = t4.d) (6 rows)
Gather Groupby/Agg
To converge the GroupBy/Agg results to a single DN, the following conditions must be met:
- The number of data rows estimated by the optimizer before and after GroupBy/Agg is less than the threshold.
- All agg subnodes are stream nodes.
gaussdb=# set explain_perf_mode=pretty; SET gaussdb=# set enable_dngather=false; SET gaussdb=# explain select count(*) from t1 group by b; id | operation | E-rows | E-width | E-costs ----+-----------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 12 | 15.87 2 | -> HashAggregate | 30 | 12 | 14.62 3 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 4 | -> Seq Scan on t1 | 30 | 4 | 14.14 (4 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select count(*) from t1 group by b; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 12 | 16.85 2 | -> HashAggregate | 30 | 12 | 14.97 3 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 14.46 4 | -> Seq Scan on t1 | 30 | 4 | 14.14 (4 rows) gaussdb=# set enable_dngather=false; SET gaussdb=# explain select b from t1 group by b; id | operation | E-rows | E-width | E-costs ----+-----------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 4 | 15.84 2 | -> HashAggregate | 30 | 4 | 14.59 3 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 4 | -> Seq Scan on t1 | 30 | 4 | 14.14 (4 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select b from t1 group by b; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 4 | 16.74 2 | -> HashAggregate | 30 | 4 | 14.87 3 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 14.46 4 | -> Seq Scan on t1 | 30 | 4 | 14.14 (4 rows)
Gather Window Function
To converge window function results to a single DN, the following conditions must be met:
- The number of data rows estimated by the optimizer before and after the window function is less than the threshold.
- All subnodes of the window function are stream nodes.
gaussdb=# set explain_perf_mode=pretty; SET gaussdb=# set enable_dngather=false; SET gaussdb=# explain select count(*) over (partition by b) a from t1; id | operation | E-rows | E-width | E-costs ----+--------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 29 | 4 | 16.71 2 | -> WindowAgg | 29 | 4 | 14.96 3 | -> Sort | 29 | 4 | 14.75 4 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 5 | -> Seq Scan on t1 | 30 | 4 | 14.14 (5 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select count(*) over (partition by b) a from t1; id | operation | E-rows | E-width | E-costs ----+----------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 4 | 19.07 2 | -> WindowAgg | 30 | 4 | 16.38 3 | -> Sort | 30 | 4 | 15.73 4 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode3) | 30 | 4 | 14.46 5 | -> Seq Scan on t1 | 30 | 4 | 14.14 (5 rows) gaussdb=# set enable_dngather=false; SET gaussdb=# explain select sum(b) over (partition by b) a from t1 group by b; id | operation | E-rows | E-width | E-costs ----+-----------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 4 | 16.18 2 | -> WindowAgg | 30 | 4 | 14.93 3 | -> Sort | 30 | 4 | 14.78 4 | -> HashAggregate | 30 | 4 | 14.59 5 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 6 | -> Seq Scan on t1 | 30 | 4 | 14.14 (6 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select sum(b) over (partition by b) a from t1 group by b; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 30 | 4 | 18.00 2 | -> WindowAgg | 30 | 4 | 16.13 3 | -> Sort | 30 | 4 | 15.68 4 | -> HashAggregate | 30 | 4 | 14.87 5 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode3) | 30 | 4 | 14.46 6 | -> Seq Scan on t1 | 30 | 4 | 14.14 (6 rows)
Union/Union all
To converge union/union all results to a single DN, the following condition must be met:
- At least one subnode must meet the requirements in the preceding three cases (Gather Join, Gather Groupby/Agg, and Gather window function).
For example, the subnodes of join are all stream nodes, and broadcast is disabled.
gaussdb=# set explain_perf_mode=pretty; SET gaussdb=# set enable_broadcast=false; SET gaussdb=# set enable_dngather=false; SET gaussdb=# explain select t1.a, t2.b from t1, t2 where t1.b = t2.b union all select t3.a, t3.b from t3, t4 where t3.b = t4.b; id | operation | E-rows | E-width | E-costs ----+--------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 60 | 8 | 65.31 2 | -> Result | 60 | 8 | 62.81 3 | -> Append(4, 10) | 60 | 8 | 62.81 4 | -> Hash Join (5,7) | 30 | 8 | 31.30 5 | -> Streaming(type: REDISTRIBUTE) | 30 | 8 | 15.49 6 | -> Seq Scan on t1 | 30 | 8 | 14.14 7 | -> Hash | 29 | 4 | 15.49 8 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 9 | -> Seq Scan on t2 | 30 | 4 | 14.14 10 | -> Hash Join (11,13) | 30 | 8 | 31.30 11 | -> Streaming(type: REDISTRIBUTE) | 30 | 8 | 15.49 12 | -> Seq Scan on t3 | 30 | 8 | 14.14 13 | -> Hash | 29 | 4 | 15.49 14 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 15 | -> Seq Scan on t4 | 30 | 4 | 14.14 (15 rows) Predicate Information (identified by plan id) ----------------------------------------------- 4 --Hash Join (5,7) Hash Cond: (t1.b = t2.b) 10 --Hash Join (11,13) Hash Cond: (t3.b = t4.b) (4 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select t1.a, t2.b from t1, t2 where t1.b = t2.b union all select t3.a, t3.b from t3, t4 where t3.b = t4.b; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 60 | 8 | 69.11 2 | -> Append(3, 9) | 60 | 8 | 65.36 3 | -> Hash Join (4,6) | 30 | 8 | 32.38 4 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 8 | 15.69 5 | -> Seq Scan on t1 | 30 | 8 | 14.14 6 | -> Hash | 30 | 4 | 15.69 7 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 15.69 8 | -> Seq Scan on t2 | 30 | 4 | 14.14 9 | -> Hash Join (10,12) | 30 | 8 | 32.38 10 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 8 | 15.69 11 | -> Seq Scan on t3 | 30 | 8 | 14.14 12 | -> Hash | 30 | 4 | 15.69 13 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode1) | 30 | 4 | 15.69 14 | -> Seq Scan on t4 | 30 | 4 | 14.14 (14 rows) Predicate Information (identified by plan id) ----------------------------------------------- 3 --Hash Join (4,6) Hash Cond: (t1.b = t2.b) 9 --Hash Join (10,12) Hash Cond: (t3.b = t4.b) (4 rows) gaussdb=# set enable_dngather=false; SET gaussdb=# explain select t1.a, t2.b from t1, t2 where t1.b = t2.b union select t3.a, t3.b from t3, t4 where t3.b = t4.b order by a, b; id | operation | E-rows | E-width | E-costs ----+-----------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 60 | 8 | 66.09 2 | -> Sort | 60 | 8 | 63.59 3 | -> HashAggregate | 60 | 8 | 63.11 4 | -> Append(5, 11) | 60 | 8 | 62.81 5 | -> Hash Join (6,8) | 30 | 8 | 31.30 6 | -> Streaming(type: REDISTRIBUTE) | 30 | 8 | 15.49 7 | -> Seq Scan on t1 | 30 | 8 | 14.14 8 | -> Hash | 29 | 4 | 15.49 9 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 10 | -> Seq Scan on t2 | 30 | 4 | 14.14 11 | -> Hash Join (12,14) | 30 | 8 | 31.30 12 | -> Streaming(type: REDISTRIBUTE) | 30 | 8 | 15.49 13 | -> Seq Scan on t3 | 30 | 8 | 14.14 14 | -> Hash | 29 | 4 | 15.49 15 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 16 | -> Seq Scan on t4 | 30 | 4 | 14.14 (16 rows) Predicate Information (identified by plan id) ----------------------------------------------- 5 --Hash Join (6,8) Hash Cond: (t1.b = t2.b) 11 --Hash Join (12,14) Hash Cond: (t3.b = t4.b) (4 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select t1.a, t2.b from t1, t2 where t1.b = t2.b union select t3.a, t3.b from t3, t4 where t3.b = t4.b order by a, b; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 60 | 8 | 71.93 2 | -> Sort | 60 | 8 | 68.18 3 | -> HashAggregate | 60 | 8 | 66.26 4 | -> Append(5, 11) | 60 | 8 | 65.36 5 | -> Hash Join (6,8) | 30 | 8 | 32.38 6 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 30 | 8 | 15.69 7 | -> Seq Scan on t1 | 30 | 8 | 14.14 8 | -> Hash | 30 | 4 | 15.69 9 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 30 | 4 | 15.69 10 | -> Seq Scan on t2 | 30 | 4 | 14.14 11 | -> Hash Join (12,14) | 30 | 8 | 32.38 12 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 30 | 8 | 15.69 13 | -> Seq Scan on t3 | 30 | 8 | 14.14 14 | -> Hash | 30 | 4 | 15.69 15 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 30 | 4 | 15.69 16 | -> Seq Scan on t4 | 30 | 4 | 14.14 (16 rows) Predicate Information (identified by plan id) ----------------------------------------------- 5 --Hash Join (6,8) Hash Cond: (t1.b = t2.b) 11 --Hash Join (12,14) Hash Cond: (t3.b = t4.b) (4 rows) gaussdb=# set enable_dngather=false; SET gaussdb=# explain select b, count(*) from t1 group by b union all select b, count(*) from t2 group by b order by b; id | operation | E-rows | E-width | E-costs ----+--------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 60 | 12 | 32.43 2 | -> Sort | 60 | 12 | 29.93 3 | -> Result | 60 | 12 | 29.45 4 | -> Append(5, 8) | 60 | 12 | 29.45 5 | -> HashAggregate | 30 | 12 | 14.62 6 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 7 | -> Seq Scan on t1 | 30 | 4 | 14.14 8 | -> HashAggregate | 30 | 12 | 14.62 9 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 10 | -> Seq Scan on t2 | 30 | 4 | 14.14 (10 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select b, count(*) from t1 group by b union all select b, count(*) from t2 group by b order by b; id | operation | E-rows | E-width | E-costs ----+-------------------------------------------------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 60 | 12 | 36.22 2 | -> Sort | 60 | 12 | 32.47 3 | -> Append(4, 7) | 60 | 12 | 30.55 4 | -> HashAggregate | 30 | 12 | 14.97 5 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 30 | 4 | 14.46 6 | -> Seq Scan on t1 | 30 | 4 | 14.14 7 | -> HashAggregate | 30 | 12 | 14.97 8 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 30 | 4 | 14.46 9 | -> Seq Scan on t2 | 30 | 4 | 14.14 (9 rows) gaussdb=# set enable_dngather=false; SET gaussdb=# explain select b, count(*) from t1 group by b union all select count(distinct a) a , count(distinct b)b from t2 order by b; id | operation | E-rows | E-width | E-costs ----+--------------------------------------------------------------+--------+---------+---------------- 1 | -> Streaming (type: GATHER) | 33 | 12 | 20000000045.02 2 | -> Sort | 33 | 12 | 20000000043.65 3 | -> Append(4, 8) | 33 | 12 | 20000000043.43 4 | -> Subquery Scan on "*SELECT* 1" | 30 | 12 | 14.72 5 | -> HashAggregate | 30 | 12 | 14.62 6 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 7 | -> Seq Scan on t1 | 30 | 4 | 14.14 8 | -> Subquery Scan on "*SELECT* 2" | 1 | 16 | 20000000028.73 9 | -> Nested Loop (10,14) | 3 | 16 | 20000000028.70 10 | -> Aggregate | 3 | 12 | 10000000014.18 11 | -> Streaming(type: BROADCAST) | 9 | 12 | 10000000014.18 12 | -> Aggregate | 3 | 12 | 14.19 13 | -> Seq Scan on t2 | 30 | 4 | 14.14 14 | -> Materialize | 3 | 8 | 10000000014.49 15 | -> Aggregate | 3 | 12 | 10000000014.48 16 | -> Streaming(type: BROADCAST) | 9 | 12 | 10000000014.48 17 | -> Aggregate | 3 | 12 | 14.48 18 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 19 | -> Seq Scan on t2 | 30 | 4 | 14.14 (19 rows) Predicate Information (identified by plan id) ----------------------------------------------- 8 --Subquery Scan on "*SELECT* 2" Filter: (Hash By "*SELECT* 2".a) (2 rows) gaussdb=# set enable_dngather=true; SET gaussdb=# explain select b, count(*) from t1 group by b union all select count(distinct a) a , count(distinct b)b from t2 order by b; id | operation | E-rows | E-width | E-costs ----+----------------------------------------------------------------------------+--------+---------+---------------- 1 | -> Streaming (type: GATHER) | 33 | 11 | 20000000046.96 2 | -> Sort | 33 | 11 | 20000000044.90 3 | -> Append(4, 8) | 33 | 11 | 20000000043.99 4 | -> Subquery Scan on "*SELECT* 1" | 30 | 12 | 15.27 5 | -> HashAggregate | 30 | 12 | 14.97 6 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 30 | 4 | 14.46 7 | -> Seq Scan on t1 | 30 | 4 | 14.14 8 | -> Streaming(type: REDISTRIBUTE ng: node_group->datanode2) | 3 | 16 | 20000000028.83 9 | -> Nested Loop (10,14) | 3 | 16 | 20000000028.70 10 | -> Aggregate | 3 | 12 | 10000000014.18 11 | -> Streaming(type: BROADCAST) | 9 | 12 | 10000000014.18 12 | -> Aggregate | 3 | 12 | 14.19 13 | -> Seq Scan on t2 | 30 | 4 | 14.14 14 | -> Materialize | 3 | 8 | 10000000014.50 15 | -> Aggregate | 3 | 12 | 10000000014.48 16 | -> Streaming(type: BROADCAST) | 9 | 12 | 10000000014.48 17 | -> Aggregate | 3 | 12 | 14.48 18 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 14.45 19 | -> Seq Scan on t2 | 30 | 4 | 14.14 (19 rows)
Parent topic: Optimization Cases
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.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot
