Comparing AntDB Cluster Plan with PGXC Remote Query Plan
AntDB version 3.1 introduces Cluster Plan, which is different from the original PGXC execution plan. Reduce Plan supports the real-time dynamic distribution of data, optimizes the execution plan that the original PGXC can not sink to the Datanode execution, and makes the execution pressure of the execution plan disperse to each Datanode. On the one hand, it reduces the performance pressure of Coordinator node, on the other hand, it submits the execution efficiency of SQL. .
Agg appears in targetlist
SQL
SELECT unique2 , ( SELECT COUNT(1) FROM onek WHERE odd > 10 ) AS cnt FROM tenk1;
Cluster Plan
QUERY PLAN -------------------------------------------------------------------------------------------- Cluster Gather (cost=974.46..4089.21 rows=10000 width=12) Plan id: 0 -> Seq Scan on tenk1 (cost=0.00..114.75 rows=2500 width=12) Plan id: 1 InitPlan 1 (returns $0) -> Cluster Reduce (cost=10.25..11.76 rows=1 width=8) Plan id: 2 -> Finalize Aggregate (cost=18.51..18.52 rows=1 width=8) Plan id: 3 -> Cluster Reduce (cost=17.89..18.50 rows=4 width=8) Plan id: 4 -> Partial Aggregate (cost=21.11..21.12 rows=1 width=8) Plan id: 5 -> Seq Scan on onek (cost=0.00..20.88 rows=95 width=0) Plan id: 6 Filter: (odd > 10) (16 rows)
Remote Query Plan
QUERY PLAN ----------------------------------------------------------------------------------------------------- Result (cost=974.46..10433.46 rows=10000 width=12) Plan id: 0 InitPlan 1 (returns $0) -> Aggregate (cost=974.45..974.46 rows=1 width=8) Plan id: 2 -> Data Node Scan on onek "_REMOTE_TABLE_QUERY__1" (cost=0.00..973.50 rows=379 width=0) Plan id: 3 Node/s: dn1, dn2, dn3, dn4 -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" (cost=0.00..9359.00 rows=10000 width=4) Plan id: 1 Node/s: dn1, dn2, dn3, dn4 (11 rows)
Agg appears in targetlist and NOT IN
SQL
SELECT unique2 , ( SELECT COUNT(1) FROM onek WHERE odd > 10 ) AS cnt FROM tenk1 WHERE even NOT IN ( SELECT COUNT(1) FROM tenk2 WHERE even < 100 );
Cluster Plan
QUERY PLAN -------------------------------------------------------------------------------------------- Cluster Gather (cost=975.64..7458.39 rows=20000 width=12) Plan id: 0 -> Seq Scan on tenk1 (cost=1.18..483.93 rows=5000 width=12) Plan id: 1 Filter: (NOT (hashed SubPlan 2)) InitPlan 1 (returns $0) -> Cluster Reduce (cost=10.25..11.76 rows=1 width=8) Plan id: 2 -> Finalize Aggregate (cost=18.51..18.52 rows=1 width=8) Plan id: 3 -> Cluster Reduce (cost=17.89..18.50 rows=4 width=8) Plan id: 4 -> Partial Aggregate (cost=21.11..21.12 rows=1 width=8) Plan id: 5 -> Seq Scan on onek (cost=0.00..20.88 rows=95 width=0) Plan id: 6 Filter: (odd > 10) SubPlan 2 -> Cluster Reduce (cost=3.21..4.71 rows=1 width=8) Plan id: 7 -> Finalize Aggregate (cost=4.42..4.43 rows=1 width=8) Plan id: 8 -> Cluster Reduce (cost=3.80..4.41 rows=4 width=8) Plan id: 9 -> Partial Aggregate (cost=3.50..3.51 rows=1 width=8) Plan id: 10 -> Seq Scan on tenk2 (cost=0.00..3.44 rows=25 width=0) Plan id: 11 Filter: (even < 100) (29 rows)
Remote Query Plan
QUERY PLAN ------------------------------------------------------------------------------------------------------------ Result (cost=974.46..38605.46 rows=20000 width=12) Plan id: 0 InitPlan 1 (returns $0) -> Aggregate (cost=974.45..974.46 rows=1 width=8) Plan id: 2 -> Data Node Scan on onek "_REMOTE_TABLE_QUERY__1" (cost=0.00..973.50 rows=379 width=0) Plan id: 3 Node/s: dn1, dn2, dn3, dn4 -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" (cost=0.00..37431.00 rows=20000 width=4) Plan id: 1 Node/s: dn1, dn2, dn3, dn4 Coordinator quals: (NOT (hashed SubPlan 2)) SubPlan 2 -> Aggregate (cost=281.00..281.01 rows=1 width=8) Plan id: 4 -> Data Node Scan on tenk2 "_REMOTE_TABLE_QUERY__2" (cost=0.00..280.75 rows=100 width=0) Plan id: 5 Node/s: dn1, dn2, dn3, dn4 (18 rows)