This is an automated email from the ASF dual-hosted git repository. maxyang pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 44a249a8cf8f342837169ed87691100088f52f41 Author: zhoujiaqi <[email protected]> AuthorDate: Mon Jan 20 15:43:57 2025 +0800 Fix: cherry-pick "Dynamic Partition Elimination, retrieving relation columns, update tests, intermedia agg" --- src/backend/gporca/libgpopt/src/base/CUtils.cpp | 2 +- src/test/regress/expected/gp_dqa.out | 339 +++++++++------------ src/test/regress/expected/gp_dqa_optimizer.out | 140 ++++----- src/test/regress/expected/gporca.out | 22 +- src/test/regress/expected/gporca_optimizer.out | 22 +- src/test/regress/expected/join_gp.out | 332 ++++++++++---------- src/test/regress/expected/join_gp_optimizer.out | 10 +- src/test/regress/expected/join_optimizer.out | 18 +- .../regress/expected/partition_prune_opfamily.out | 52 ++-- src/test/regress/expected/partition_pruning.out | 166 +++++----- src/test/regress/sql/gp_dqa.sql | 5 +- src/test/regress/sql/join_gp.sql | 2 +- 12 files changed, 522 insertions(+), 588 deletions(-) diff --git a/src/backend/gporca/libgpopt/src/base/CUtils.cpp b/src/backend/gporca/libgpopt/src/base/CUtils.cpp index 63c1a7d1ff..efa6679a3a 100644 --- a/src/backend/gporca/libgpopt/src/base/CUtils.cpp +++ b/src/backend/gporca/libgpopt/src/base/CUtils.cpp @@ -3837,7 +3837,7 @@ CUtils::PexprFuncElemExpr(CMemoryPool *mp, CMDAccessor *md_accessor, cast_func->GetResultTypeMdid()->AddRef(); CScalarFunc *popCastScalarFunc = GPOS_NEW(mp) CScalarFunc(mp, mdid_func, cast_func->GetResultTypeMdid(), - typmod, pstrFunc, false /* funcvariadic */); + typmod, pstrFunc, 1 /* Explicit Cast */, false /* funcvariadic */); mdid_elem_type->AddRef(); CExpression *pexprCaseTest = GPOS_NEW(mp) CExpression(mp, GPOS_NEW(mp) CScalarCaseTest(mp, mdid_elem_type)); diff --git a/src/test/regress/expected/gp_dqa.out b/src/test/regress/expected/gp_dqa.out index af9ee92998..a6af271af7 100644 --- a/src/test/regress/expected/gp_dqa.out +++ b/src/test/regress/expected/gp_dqa.out @@ -2395,6 +2395,7 @@ explain (verbose, costs off) select count(distinct (b)::text) as b, count(distin Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' (19 rows) +drop table dqa_f3; -- Test 3-phase agg for DISTINCT on distribution keys -- or DISTINCT when GROUP BY on distribution keys create table t_issue_659(a int, b int) using ao_row; @@ -2624,9 +2625,9 @@ select id, count(distinct a), avg(b), sum(c) from num_table group by grouping se (3 rows) reset optimizer_force_multistage_agg; -reset optimizer_enable_use_distribution_in_dqa; -drop table t_issue_659; -- DQA with Agg(Intermediate Agg) +-- In PG optimizer Intermediate Agg have not support yet +-- Current test cases will be changed after commit 971fa82(Support intermediate Agg in planner (#13959)) set enable_hashagg=on; set enable_groupagg=off; create table dqa_f3(a int, b int, c int, d int, e int ) distributed by (a); @@ -2650,23 +2651,23 @@ analyze dqa_f3; * */ explain (verbose on, costs off)select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - Output: (sum(a)), (count(b)), (sum(c)), e - -> Finalize HashAggregate - Output: sum(a), count(b), sum(c), e + Output: (sum(DISTINCT a)), (count(b)), (sum(c)), e + -> GroupAggregate + Output: sum(DISTINCT a), count(b), sum(c), e Group Key: dqa_f3.e - -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: e, a, (PARTIAL count(b)), (PARTIAL sum(c)) - Hash Key: e - -> Partial HashAggregate - Output: e, a, PARTIAL count(b), PARTIAL sum(c) - Group Key: dqa_f3.e, dqa_f3.a + -> Sort + Output: e, a, b, c + Sort Key: dqa_f3.e + -> Redistribute Motion 3:3 (slice2; segments: 3) + Output: e, a, b, c + Hash Key: e -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + Output: e, a, b, c + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' (15 rows) select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e; @@ -2682,20 +2683,20 @@ select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e; (7 rows) explain (verbose on, costs off) select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - Output: (sum(e)), (count(b)), (sum(c)), a - -> Finalize HashAggregate - Output: sum(e), count(b), sum(c), a + Output: (sum(DISTINCT e)), (count(b)), (sum(c)), a + -> GroupAggregate + Output: sum(DISTINCT e), count(b), sum(c), a Group Key: dqa_f3.a - -> Partial HashAggregate - Output: a, e, PARTIAL count(b), PARTIAL sum(c) - Group Key: dqa_f3.a, dqa_f3.e + -> Sort + Output: a, e, b, c + Sort Key: dqa_f3.a -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + Output: a, e, b, c + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' (12 rows) select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a; @@ -2731,27 +2732,24 @@ select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a; * */ explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - Output: (sum(c)), (count(a)), (sum(d)), b - -> Finalize HashAggregate - Output: sum(c), count(a), sum(d), b + Output: (sum(DISTINCT c)), (count(a)), (sum(d)), b + -> GroupAggregate + Output: sum(DISTINCT c), count(a), sum(d), b Group Key: dqa_f3.b - -> Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c + -> Sort + Output: b, c, a, d + Sort Key: dqa_f3.b -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) + Output: b, c, a, d Hash Key: b - -> Streaming Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + -> Seq Scan on public.dqa_f3 + Output: b, c, a, d + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(18 rows) +(15 rows) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; sum | count | sum @@ -2764,31 +2762,25 @@ select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; (5 rows) explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - Output: (sum(c)), (count(a)), (sum(d)), b + Output: (sum(DISTINCT c)), (count(a)), (sum(d)), b Merge Key: b - -> Sort - Output: (sum(c)), (count(a)), (sum(d)), b - Sort Key: dqa_f3.b - -> Finalize HashAggregate - Output: sum(c), count(a), sum(d), b - Group Key: dqa_f3.b - -> Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c - -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) - Hash Key: b - -> Streaming Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + -> GroupAggregate + Output: sum(DISTINCT c), count(a), sum(d), b + Group Key: dqa_f3.b + -> Sort + Output: b, c, a, d + Sort Key: dqa_f3.b + -> Redistribute Motion 3:3 (slice2; segments: 3) + Output: b, c, a, d + Hash Key: b + -> Seq Scan on public.dqa_f3 + Output: b, c, a, d + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(22 rows) +(16 rows) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b; sum | count | sum @@ -2801,33 +2793,30 @@ select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b; (5 rows) explain (verbose on, costs off) select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; - QUERY PLAN --------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ HashAggregate - Output: (sum(c)), (count(a)), (sum(d)), b - Group Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d)) + Output: (sum(DISTINCT c)), (count(a)), (sum(d)), b + Group Key: (sum(DISTINCT dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d)) -> Gather Motion 3:1 (slice1; segments: 3) - Output: (sum(c)), (count(a)), (sum(d)), b + Output: (sum(DISTINCT c)), (count(a)), (sum(d)), b -> HashAggregate - Output: (sum(c)), (count(a)), (sum(d)), b - Group Key: sum(dqa_f3.c), count(dqa_f3.a), sum(dqa_f3.d) - -> Finalize HashAggregate - Output: sum(c), count(a), sum(d), b + Output: (sum(DISTINCT c)), (count(a)), (sum(d)), b + Group Key: sum(DISTINCT dqa_f3.c), count(dqa_f3.a), sum(dqa_f3.d) + -> GroupAggregate + Output: sum(DISTINCT c), count(a), sum(d), b Group Key: dqa_f3.b - -> Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c + -> Sort + Output: b, c, a, d + Sort Key: dqa_f3.b -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) + Output: b, c, a, d Hash Key: b - -> Streaming Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + -> Seq Scan on public.dqa_f3 + Output: b, c, a, d + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(24 rows) +(21 rows) select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; sum | count | sum @@ -2840,28 +2829,25 @@ select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; (5 rows) explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3; - QUERY PLAN ------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - Output: (sum(c)), (count(a)), (sum(d)), b - -> Finalize HashAggregate - Output: sum(c), count(a), sum(d), b + Output: (sum(DISTINCT c)), (count(a)), (sum(d)), b + -> GroupAggregate + Output: sum(DISTINCT c), count(a), sum(d), b Group Key: dqa_f3.b Filter: (avg(dqa_f3.e) > '3'::numeric) - -> Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d), PARTIAL avg(e) - Group Key: dqa_f3.b, dqa_f3.c + -> Sort + Output: b, c, a, d, e + Sort Key: dqa_f3.b -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)), (PARTIAL avg(e)) + Output: b, c, a, d, e Hash Key: b - -> Streaming Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d), PARTIAL avg(e) - Group Key: dqa_f3.b, dqa_f3.c - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + -> Seq Scan on public.dqa_f3 + Output: b, c, a, d, e + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(19 rows) +(16 rows) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3; sum | count | sum @@ -2875,37 +2861,34 @@ explain (verbose on, costs off) select sum(Distinct sub.c), count(a), sum(d) from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub on sub.x = dqa_f3.e group by b; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - Output: (sum((COALESCE(dqa_f2.y, 5)))), (count(dqa_f3.a)), (sum(dqa_f3.d)), dqa_f3.b - -> Finalize HashAggregate - Output: sum((COALESCE(dqa_f2.y, 5))), count(dqa_f3.a), sum(dqa_f3.d), dqa_f3.b + Output: (sum(DISTINCT (COALESCE(dqa_f2.y, 5)))), (count(dqa_f3.a)), (sum(dqa_f3.d)), dqa_f3.b + -> GroupAggregate + Output: sum(DISTINCT (COALESCE(dqa_f2.y, 5))), count(dqa_f3.a), sum(dqa_f3.d), dqa_f3.b Group Key: dqa_f3.b - -> Partial HashAggregate - Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), PARTIAL count(dqa_f3.a), PARTIAL sum(dqa_f3.d) - Group Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5)) + -> Sort + Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), dqa_f3.a, dqa_f3.d + Sort Key: dqa_f3.b -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), (PARTIAL count(dqa_f3.a)), (PARTIAL sum(dqa_f3.d)) + Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), dqa_f3.a, dqa_f3.d Hash Key: dqa_f3.b - -> Streaming Partial HashAggregate - Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), PARTIAL count(dqa_f3.a), PARTIAL sum(dqa_f3.d) - Group Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5)) - -> Hash Right Join - Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), dqa_f3.a, dqa_f3.d - Hash Cond: (dqa_f2.x = dqa_f3.e) - -> Seq Scan on public.dqa_f2 - Output: dqa_f2.x, COALESCE(dqa_f2.y, 5) - -> Hash + -> Hash Right Join + Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), dqa_f3.a, dqa_f3.d + Hash Cond: (dqa_f2.x = dqa_f3.e) + -> Seq Scan on public.dqa_f2 + Output: dqa_f2.x, COALESCE(dqa_f2.y, 5) + -> Hash + Output: dqa_f3.a, dqa_f3.d, dqa_f3.b, dqa_f3.e + -> Redistribute Motion 3:3 (slice3; segments: 3) Output: dqa_f3.a, dqa_f3.d, dqa_f3.b, dqa_f3.e - -> Redistribute Motion 3:3 (slice3; segments: 3) + Hash Key: dqa_f3.e + -> Seq Scan on public.dqa_f3 Output: dqa_f3.a, dqa_f3.d, dqa_f3.b, dqa_f3.e - Hash Key: dqa_f3.e - -> Seq Scan on public.dqa_f3 - Output: dqa_f3.a, dqa_f3.d, dqa_f3.b, dqa_f3.e + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(28 rows) +(25 rows) select sum(Distinct sub.c), count(a), sum(d) from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub @@ -2922,23 +2905,23 @@ select sum(Distinct sub.c), count(a), sum(d) -- Test gp_enable_agg_distinct_pruning is off on this branch set gp_enable_agg_distinct_pruning = off; explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; - QUERY PLAN ---------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - Output: (sum(c)), (count(a)), (sum(d)), b - -> Finalize HashAggregate - Output: sum(c), count(a), sum(d), b + Output: (sum(DISTINCT c)), (count(a)), (sum(d)), b + -> GroupAggregate + Output: sum(DISTINCT c), count(a), sum(d), b Group Key: dqa_f3.b - -> Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c + -> Sort + Output: b, c, a, d + Sort Key: dqa_f3.b -> Redistribute Motion 3:3 (slice2; segments: 3) Output: b, c, a, d Hash Key: b -> Seq Scan on public.dqa_f3 Output: b, c, a, d + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_enable_agg_distinct_pruning = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_enable_agg_distinct_pruning = 'off', gp_motion_cost_per_row = '1' (15 rows) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; @@ -2964,26 +2947,22 @@ reset gp_enable_agg_distinct_pruning; * -> input */ explain (verbose on, costs off) select sum(Distinct b), count(c), sum(a) from dqa_f3; - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate - Output: sum(b), count(c), sum(a) + Output: sum(DISTINCT b), count(c), sum(a) -> Gather Motion 3:1 (slice1; segments: 3) - Output: b, (PARTIAL count(c)), (PARTIAL sum(a)) - -> Partial HashAggregate - Output: b, PARTIAL count(c), PARTIAL sum(a) - Group Key: dqa_f3.b + Output: (PARTIAL sum(DISTINCT b)), (PARTIAL count(c)), (PARTIAL sum(a)) + -> Partial Aggregate + Output: PARTIAL sum(DISTINCT b), PARTIAL count(c), PARTIAL sum(a) -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, (PARTIAL count(c)), (PARTIAL sum(a)) + Output: b, c, a Hash Key: b - -> Streaming Partial HashAggregate - Output: b, PARTIAL count(c), PARTIAL sum(a) - Group Key: dqa_f3.b - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + -> Seq Scan on public.dqa_f3 + Output: b, c, a + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(17 rows) +(13 rows) select sum(Distinct b), count(c), sum(a) from dqa_f3; sum | count | sum @@ -2992,32 +2971,22 @@ select sum(Distinct b), count(c), sum(a) from dqa_f3; (1 row) explain (verbose on, costs off) select distinct sum(Distinct b), count(c), sum(a) from dqa_f3; - QUERY PLAN ------------------------------------------------------------------------------------ - Unique - Output: (sum(b)), (count(c)), (sum(a)) - Group Key: (sum(b)), (count(c)), (sum(a)) - -> Sort - Output: (sum(b)), (count(c)), (sum(a)) - Sort Key: (sum(dqa_f3.b)), (count(dqa_f3.c)), (sum(dqa_f3.a)) - -> Finalize Aggregate - Output: sum(b), count(c), sum(a) - -> Gather Motion 3:1 (slice1; segments: 3) - Output: b, (PARTIAL count(c)), (PARTIAL sum(a)) - -> Partial HashAggregate - Output: b, PARTIAL count(c), PARTIAL sum(a) - Group Key: dqa_f3.b - -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, (PARTIAL count(c)), (PARTIAL sum(a)) - Hash Key: b - -> Streaming Partial HashAggregate - Output: b, PARTIAL count(c), PARTIAL sum(a) - Group Key: dqa_f3.b - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ + Finalize Aggregate + Output: sum(DISTINCT b), count(c), sum(a) + -> Gather Motion 3:1 (slice1; segments: 3) + Output: (PARTIAL sum(DISTINCT b)), (PARTIAL count(c)), (PARTIAL sum(a)) + -> Partial Aggregate + Output: PARTIAL sum(DISTINCT b), PARTIAL count(c), PARTIAL sum(a) + -> Redistribute Motion 3:3 (slice2; segments: 3) + Output: b, c, a + Hash Key: b + -> Seq Scan on public.dqa_f3 + Output: b, c, a + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(23 rows) +(13 rows) select distinct sum(Distinct b), count(c), sum(a) from dqa_f3; sum | count | sum @@ -3026,26 +2995,22 @@ select distinct sum(Distinct b), count(c), sum(a) from dqa_f3; (1 row) explain (verbose on, costs off) select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate - Output: sum(b), count(c) FILTER (WHERE (c > 1)), sum(a) + Output: sum(DISTINCT b), count(c) FILTER (WHERE (c > 1)), sum(a) -> Gather Motion 3:1 (slice1; segments: 3) - Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a)) - -> Partial HashAggregate - Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a) - Group Key: dqa_f3.b + Output: (PARTIAL sum(DISTINCT b)), (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a)) + -> Partial Aggregate + Output: PARTIAL sum(DISTINCT b), PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a) -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a)) + Output: b, c, a Hash Key: b - -> Streaming Partial HashAggregate - Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a) - Group Key: dqa_f3.b - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + -> Seq Scan on public.dqa_f3 + Output: b, c, a + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'off' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(17 rows) +(13 rows) select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3; sum | count | sum diff --git a/src/test/regress/expected/gp_dqa_optimizer.out b/src/test/regress/expected/gp_dqa_optimizer.out index 2cf2943d35..1f8cd945e8 100644 --- a/src/test/regress/expected/gp_dqa_optimizer.out +++ b/src/test/regress/expected/gp_dqa_optimizer.out @@ -2548,6 +2548,7 @@ DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disab Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' (19 rows) +drop table dqa_f3; -- Test 3-phase agg for DISTINCT on distribution keys -- or DISTINCT when GROUP BY on distribution keys create table t_issue_659(a int, b int) using ao_row; @@ -2808,9 +2809,9 @@ select id, count(distinct a), avg(b), sum(c) from num_table group by grouping se (3 rows) reset optimizer_force_multistage_agg; -reset optimizer_enable_use_distribution_in_dqa; -drop table t_issue_659; -- DQA with Agg(Intermediate Agg) +-- In PG optimizer Intermediate Agg have not support yet +-- Current test cases will be changed after commit 971fa82(Support intermediate Agg in planner (#13959)) set enable_hashagg=on; set enable_groupagg=off; create table dqa_f3(a int, b int, c int, d int, e int ) distributed by (a); @@ -2844,7 +2845,7 @@ explain (verbose on, costs off)select sum(Distinct a), count(b), sum(c) from dqa -> Redistribute Motion 3:3 (slice2; segments: 3) Output: a, e, (PARTIAL count(b)), (PARTIAL sum(c)) Hash Key: e - -> Partial HashAggregate + -> Streaming Partial HashAggregate Output: a, e, PARTIAL count(b), PARTIAL sum(c) Group Key: dqa_f3.e, dqa_f3.a -> Seq Scan on public.dqa_f3 @@ -2873,7 +2874,7 @@ explain (verbose on, costs off) select sum(Distinct e), count(b), sum(c) from dq -> Finalize HashAggregate Output: sum(e), count(b), sum(c) Group Key: dqa_f3.a - -> Partial HashAggregate + -> Streaming Partial HashAggregate Output: PARTIAL count(b), PARTIAL sum(c), a, e Group Key: dqa_f3.a, dqa_f3.e -> Seq Scan on public.dqa_f3 @@ -2999,8 +3000,8 @@ select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b; (5 rows) explain (verbose on, costs off) select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Output: (sum(c)), (count(a)), (sum(d)) -> GroupAggregate @@ -3012,35 +3013,29 @@ explain (verbose on, costs off) select distinct sum(Distinct c), count(a), sum(d -> Redistribute Motion 3:3 (slice2; segments: 3) Output: (sum(c)), (count(a)), (sum(d)) Hash Key: (sum(c)), (count(a)), (sum(d)) - -> GroupAggregate - Output: (sum(c)), (count(a)), (sum(d)) - Group Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d)) - -> Sort - Output: (sum(c)), (count(a)), (sum(d)), b - Sort Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d)) - -> Finalize HashAggregate - Output: sum(c), count(a), sum(d), b - Group Key: dqa_f3.b - -> Redistribute Motion 3:3 (slice3; segments: 3) + -> Finalize HashAggregate + Output: sum(c), count(a), sum(d) + Group Key: dqa_f3.b + -> Redistribute Motion 3:3 (slice3; segments: 3) + Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) + Hash Key: b + -> Partial GroupAggregate + Output: b, c, PARTIAL count(a), PARTIAL sum(d) + Group Key: dqa_f3.b, dqa_f3.c + -> Sort Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) - Hash Key: b - -> Partial GroupAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c - -> Sort - Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) - Sort Key: dqa_f3.b, dqa_f3.c - -> Redistribute Motion 3:3 (slice4; segments: 3) - Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) - Hash Key: b, c - -> Streaming Partial HashAggregate - Output: b, c, PARTIAL count(a), PARTIAL sum(d) - Group Key: dqa_f3.b, dqa_f3.c - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d + Sort Key: dqa_f3.b, dqa_f3.c + -> Redistribute Motion 3:3 (slice4; segments: 3) + Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)) + Hash Key: b, c + -> Streaming Partial HashAggregate + Output: b, c, PARTIAL count(a), PARTIAL sum(d) + Group Key: dqa_f3.b, dqa_f3.c + -> Seq Scan on public.dqa_f3 + Output: a, b, c, d + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'on' Optimizer: Pivotal Optimizer (GPORCA) - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(39 rows) +(33 rows) select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b; sum | count | sum @@ -3262,26 +3257,22 @@ select distinct sum(Distinct b), count(c), sum(a) from dqa_f3; explain (verbose on, costs off) select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3; INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Aggregate functions with FILTER - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate - Output: sum(b), count(c) FILTER (WHERE (c > 1)), sum(a) + Output: sum(DISTINCT b), count(c) FILTER (WHERE (c > 1)), sum(a) -> Gather Motion 3:1 (slice1; segments: 3) - Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a)) - -> Partial HashAggregate - Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a) - Group Key: dqa_f3.b + Output: (PARTIAL sum(DISTINCT b)), (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a)) + -> Partial Aggregate + Output: PARTIAL sum(DISTINCT b), PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a) -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a)) + Output: b, c, a Hash Key: b - -> Streaming Partial HashAggregate - Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a) - Group Key: dqa_f3.b - -> Seq Scan on public.dqa_f3 - Output: a, b, c, d, e + -> Seq Scan on public.dqa_f3 + Output: b, c, a + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'on' Optimizer: Postgres query optimizer - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1' -(17 rows) +(13 rows) select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3; INFO: GPORCA failed to produce a plan, falling back to planner @@ -3311,63 +3302,50 @@ DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disab set optimizer_enable_multiple_distinct_aggs=on; explain (verbose on, costs off) select count(distinct a), count(distinct b) from dqa_f4 group by c; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b)) -> Sequence Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b)) -> Shared Scan (share slice:id 1:0) - Output: share0_ref1.a, share0_ref1.b, share0_ref1.c, share0_ref1.ctid, share0_ref1.xmin, share0_ref1.cmin, share0_ref1.xmax, share0_ref1.cmax, share0_ref1.tableoid, share0_ref1.gp_segment_id + Output: share0_ref1.a, share0_ref1.b, share0_ref1.c, share0_ref1.ctid, share0_ref1.xmin, share0_ref1.cmin, share0_ref1.xmax, share0_ref1.cmax, share0_ref1.tableoid, share0_ref1.gp_segment_id, share0_ref1.gp_foreign_server -> Seq Scan on public.dqa_f4 - Output: dqa_f4.a, dqa_f4.b, dqa_f4.c, dqa_f4.ctid, dqa_f4.xmin, dqa_f4.cmin, dqa_f4.xmax, dqa_f4.cmax, dqa_f4.tableoid, dqa_f4.gp_segment_id + Output: dqa_f4.a, dqa_f4.b, dqa_f4.c, dqa_f4.ctid, dqa_f4.xmin, dqa_f4.cmin, dqa_f4.xmax, dqa_f4.cmax, dqa_f4.tableoid, dqa_f4.gp_segment_id, dqa_f4.gp_foreign_server -> Hash Join Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b)) Hash Cond: (NOT (share0_ref3.c IS DISTINCT FROM share0_ref2.c)) - -> Finalize GroupAggregate + -> GroupAggregate Output: count(DISTINCT share0_ref3.a), share0_ref3.c Group Key: share0_ref3.c -> Sort - Output: share0_ref3.c, (PARTIAL count(DISTINCT share0_ref3.a)) + Output: share0_ref3.a, share0_ref3.c Sort Key: share0_ref3.c -> Redistribute Motion 3:3 (slice2; segments: 3) - Output: share0_ref3.c, (PARTIAL count(DISTINCT share0_ref3.a)) + Output: share0_ref3.a, share0_ref3.c Hash Key: share0_ref3.c - -> Partial GroupAggregate - Output: share0_ref3.c, PARTIAL count(DISTINCT share0_ref3.a) - Group Key: share0_ref3.c - -> Sort - Output: share0_ref3.a, share0_ref3.b, share0_ref3.c, share0_ref3.ctid, share0_ref3.xmin, share0_ref3.cmin, share0_ref3.xmax, share0_ref3.cmax, share0_ref3.tableoid, share0_ref3.gp_segment_id - Sort Key: share0_ref3.c, share0_ref3.a - -> Shared Scan (share slice:id 2:0) - Output: share0_ref3.a, share0_ref3.b, share0_ref3.c, share0_ref3.ctid, share0_ref3.xmin, share0_ref3.cmin, share0_ref3.xmax, share0_ref3.cmax, share0_ref3.tableoid, share0_ref3.gp_segment_id + -> Result + Output: share0_ref3.a, share0_ref3.c + -> Shared Scan (share slice:id 2:0) + Output: share0_ref3.a, share0_ref3.b, share0_ref3.c, share0_ref3.ctid, share0_ref3.xmin, share0_ref3.cmin, share0_ref3.xmax, share0_ref3.cmax, share0_ref3.tableoid, share0_ref3.gp_segment_id, share0_ref3.gp_foreign_server -> Hash Output: (count(DISTINCT share0_ref2.b)), share0_ref2.c - -> Finalize GroupAggregate + -> GroupAggregate Output: count(DISTINCT share0_ref2.b), share0_ref2.c Group Key: share0_ref2.c -> Sort - Output: share0_ref2.c, (PARTIAL count(DISTINCT share0_ref2.b)) + Output: share0_ref2.b, share0_ref2.c Sort Key: share0_ref2.c -> Redistribute Motion 3:3 (slice3; segments: 3) - Output: share0_ref2.c, (PARTIAL count(DISTINCT share0_ref2.b)) + Output: share0_ref2.b, share0_ref2.c Hash Key: share0_ref2.c - -> Partial GroupAggregate - Output: share0_ref2.c, PARTIAL count(DISTINCT share0_ref2.b) - Group Key: share0_ref2.c - -> Sort - Output: share0_ref2.b, share0_ref2.c - Sort Key: share0_ref2.c, share0_ref2.b - -> Redistribute Motion 3:3 (slice4; segments: 3) - Output: share0_ref2.b, share0_ref2.c - Hash Key: share0_ref2.b - -> Result - Output: share0_ref2.b, share0_ref2.c - -> Shared Scan (share slice:id 4:0) - Output: share0_ref2.a, share0_ref2.b, share0_ref2.c, share0_ref2.ctid, share0_ref2.xmin, share0_ref2.cmin, share0_ref2.xmax, share0_ref2.cmax, share0_ref2.tableoid, share0_ref2.gp_segment_id + -> Result + Output: share0_ref2.b, share0_ref2.c + -> Shared Scan (share slice:id 3:0) + Output: share0_ref2.a, share0_ref2.b, share0_ref2.c, share0_ref2.ctid, share0_ref2.xmin, share0_ref2.cmin, share0_ref2.xmax, share0_ref2.cmax, share0_ref2.tableoid, share0_ref2.gp_segment_id, share0_ref2.gp_foreign_server + Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel = 'off', gp_motion_cost_per_row = '2', optimizer = 'on' Optimizer: Pivotal Optimizer (GPORCA) - Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1', optimizer_enable_multiple_distinct_aggs = 'on' -(54 rows) +(41 rows) select count(distinct a), count(distinct b) from dqa_f4 group by c; count | count diff --git a/src/test/regress/expected/gporca.out b/src/test/regress/expected/gporca.out index 66a7be1b10..dd7932e40b 100644 --- a/src/test/regress/expected/gporca.out +++ b/src/test/regress/expected/gporca.out @@ -14579,17 +14579,6 @@ explain select * from ts_tbl where ts = to_timestamp('99991231'::text, 'YYYYMMDD Optimizer: Postgres query optimizer (4 rows) -WITH conf AS ( - SELECT setting - FROM pg_catalog.pg_config - WHERE name = 'LDFLAGS_EX') -select * from conf; - setting ---------- - -(1 row) - -reset optimizer_trace_fallback; -- Test ORCA support for implicit array coerce cast -- ORCA should generate a valid plan passing along the cast function as part of ArrayCoerceExpr -- While execution thin insert query fails due to the mismatch of column length. @@ -14610,6 +14599,17 @@ explain insert into array_coerce_foo select * from array_coerce_bar; insert into array_coerce_foo select * from array_coerce_bar; ERROR: value too long for type character varying(2) (seg1 127.0.0.1:7003 pid=55908) +WITH conf AS ( + SELECT setting + FROM pg_catalog.pg_config + WHERE name = 'LDFLAGS_EX') +select * from conf; + setting +--------- + +(1 row) + +reset optimizer_trace_fallback; -- These testcases will fallback to postgres when "PexprConvert2In" is enabled if -- underlying issues are not fixed create table baz (a int,b int); diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out index 78ec799376..f53f05999a 100644 --- a/src/test/regress/expected/gporca_optimizer.out +++ b/src/test/regress/expected/gporca_optimizer.out @@ -14695,17 +14695,6 @@ explain select * from ts_tbl where ts = to_timestamp('99991231'::text, 'YYYYMMDD Optimizer: Pivotal Optimizer (GPORCA) (4 rows) -WITH conf AS ( - SELECT setting - FROM pg_catalog.pg_config - WHERE name = 'LDFLAGS_EX') -select * from conf; - setting ---------- - -(1 row) - -reset optimizer_trace_fallback; -- Test ORCA support for implicit array coerce cast -- ORCA should generate a valid plan passing along the cast function as part of ArrayCoerceExpr -- While execution thin insert query fails due to the mismatch of column length. @@ -14726,6 +14715,17 @@ explain insert into array_coerce_foo select * from array_coerce_bar; insert into array_coerce_foo select * from array_coerce_bar; ERROR: value too long for type character varying(2) (seg1 127.0.0.1:7003 pid=51460) +WITH conf AS ( + SELECT setting + FROM pg_catalog.pg_config + WHERE name = 'LDFLAGS_EX') +select * from conf; + setting +--------- + +(1 row) + +reset optimizer_trace_fallback; -- These testcases will fallback to postgres when "PexprConvert2In" is enabled if -- underlying issues are not fixed create table baz (a int,b int); diff --git a/src/test/regress/expected/join_gp.out b/src/test/regress/expected/join_gp.out index d85607dbcd..e1d8aa6831 100644 --- a/src/test/regress/expected/join_gp.out +++ b/src/test/regress/expected/join_gp.out @@ -1884,7 +1884,7 @@ EXPLAIN select t1.* from t1 left join (t2 left join t3 on t3.a=t2.b) on t2.a=t1. (3 rows) -- inner table is a derived table -EXPLAIN +EXPLAIN (COSTS OFF) select t1.* from t1 left join ( select t2.b as v2b, count(*) as v2c @@ -1892,10 +1892,10 @@ select t1.* from t1 left join group by t2.b ) v2 on v2.v2b=t1.a; - QUERY PLAN ------------------------------------------------------------------------------------ - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1639.00 rows=96300 width=4) - -> Seq Scan on t1 (cost=0.00..355.00 rows=32100 width=4) + QUERY PLAN +------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) + -> Seq Scan on t1 Optimizer: Postgres query optimizer (3 rows) @@ -1925,17 +1925,17 @@ explain select fooJoinPruning.* from fooJoinPruning left join barJoinPruning on -- Unique key of inner relation ie 'p' is present in the join condition and is equal to a column from outer relation but output columns are from inner relation -- explain select barJoinPruning.* from fooJoinPruning left join barJoinPruning on barJoinPruning.p=fooJoinPruning.b where fooJoinPruning.b>1000; - QUERY PLAN ---------------------------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=639.89..1587.04 rows=25967 width=12) - -> Hash Right Join (cost=639.89..1240.81 rows=8656 width=12) - Hash Cond: (barjoinpruning.p = foojoinpruning.b) - -> Seq Scan on barjoinpruning (cost=0.00..293.67 rows=25967 width=12) - -> Hash (cost=531.69..531.69 rows=8656 width=4) - -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..531.69 rows=8656 width=4) - Hash Key: foojoinpruning.b - -> Seq Scan on foojoinpruning (cost=0.00..358.58 rows=8656 width=4) - Filter: (b > 1000) + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=618.25..1605.44 rows=25967 width=12) + -> Hash Left Join (cost=618.25..1259.22 rows=8656 width=12) + Hash Cond: (foojoinpruning.b = barjoinpruning.p) + -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..531.69 rows=8656 width=4) + Hash Key: foojoinpruning.b + -> Seq Scan on foojoinpruning (cost=0.00..358.58 rows=8656 width=4) + Filter: (b > 1000) + -> Hash (cost=293.67..293.67 rows=25967 width=12) + -> Seq Scan on barjoinpruning (cost=0.00..293.67 rows=25967 width=12) Optimizer: Postgres query optimizer (10 rows) @@ -2134,19 +2134,19 @@ analyze bar_List_PT2; -- FOR RANGE PARTITIONED TABLE -- Outer table: Partitioned table, Join Condition on Partition key: Yes, Result: DPE - YES explain (costs off) select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_pt1_1_prt_1.a1_pc = foo.a) + Hash Cond: (bar_pt1.a1_pc = foo.a) -> Append Partition Selectors: $0 - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Partition Selector (selector id: $0) -> Seq Scan on foo @@ -2165,20 +2165,20 @@ select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a; -- Outer table: Partitioned table, Join Condition on Partition key: No, Result: DPE - No explain (costs off) select * from bar_PT1 right join foo on bar_PT1.b1 =foo.a; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_pt1_1_prt_1.b1 = foo.a) + Hash Cond: (bar_pt1.b1 = foo.a) -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: bar_pt1_1_prt_1.b1 + Hash Key: bar_pt1.b1 -> Append - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Seq Scan on foo Optimizer: Postgres query optimizer @@ -2196,25 +2196,25 @@ select * from bar_PT1 right join foo on bar_PT1.b1 =foo.a; -- Outer,Inner table: Partitioned table, Join Condition on Partition key: Yes, Result: DPE - Yes explain (costs off) select * from bar_PT1 right join bar_PT3 on bar_PT1.a1_PC =bar_PT3.a3_PC; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_pt1_1_prt_1.a1_pc = bar_pt3_1_prt_1.a3_pc) + Hash Cond: (bar_pt1.a1_pc = bar_pt3.a3_pc) -> Append Partition Selectors: $0 - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Partition Selector (selector id: $0) -> Append - -> Seq Scan on bar_pt3_1_prt_1 - -> Seq Scan on bar_pt3_1_prt_2 - -> Seq Scan on bar_pt3_1_prt_3 + -> Seq Scan on bar_pt3_1_prt_1 bar_pt3_1 + -> Seq Scan on bar_pt3_1_prt_2 bar_pt3_2 + -> Seq Scan on bar_pt3_1_prt_3 bar_pt3_3 Optimizer: Postgres query optimizer (18 rows) @@ -2230,18 +2230,18 @@ select * from bar_PT1 right join bar_PT3 on bar_PT1.a1_PC =bar_PT3.a3_PC; -- Outer table: Not a Partitioned table, Join Condition on Partition key: Yes, Result: DPE - No explain (costs off) select * from foo right join bar_PT1 on foo.a=bar_PT1.a1_PC; - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Left Join - Hash Cond: (bar_pt1_1_prt_1.a1_pc = foo.a) + Hash Cond: (bar_pt1.a1_pc = foo.a) -> Append - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Seq Scan on foo Optimizer: Postgres query optimizer @@ -2266,19 +2266,19 @@ select * from foo right join bar_PT1 on foo.a=bar_PT1.a1_PC; -- Right join with predicate on the column of non partitioned table in 'where clause'. -- Result: DPE - Yes, explain (costs off) select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a where foo.a>2; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_pt1_1_prt_1.a1_pc = foo.a) + Hash Cond: (bar_pt1.a1_pc = foo.a) -> Append Partition Selectors: $0 - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Partition Selector (selector id: $0) -> Seq Scan on foo @@ -2296,19 +2296,19 @@ select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a where foo.a>2; --Conjunction in join condition, Result: DPE - Yes explain (costs off) select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a and bar_PT1.b1 =foo.b; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: ((bar_pt1_1_prt_1.a1_pc = foo.a) AND (bar_pt1_1_prt_1.b1 = foo.b)) + Hash Cond: ((bar_pt1.a1_pc = foo.a) AND (bar_pt1.b1 = foo.b)) -> Append Partition Selectors: $0 - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Partition Selector (selector id: $0) -> Seq Scan on foo @@ -2326,20 +2326,20 @@ select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a and bar_PT1.b1 =foo (5 rows) explain (costs off) select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a and foo.b>2; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_pt1_1_prt_1.a1_pc = foo.a) + Hash Cond: (bar_pt1.a1_pc = foo.a) Join Filter: (foo.b > 2) -> Append Partition Selectors: $0 - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Partition Selector (selector id: $0) -> Seq Scan on foo @@ -2358,31 +2358,31 @@ select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a and foo.b>2; -- Multiple Right Joins, DPE- Yes explain (costs off) select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a right join bar_PT2 on bar_PT1.a1_PC =bar_PT2.b2_PC; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Left Join - Hash Cond: (bar_pt2_1_prt_1.b2_pc = bar_pt1_1_prt_1.a1_pc) + Hash Cond: (bar_pt2.b2_pc = bar_pt1.a1_pc) -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: bar_pt2_1_prt_1.b2_pc + Hash Key: bar_pt2.b2_pc -> Append - -> Seq Scan on bar_pt2_1_prt_1 - -> Seq Scan on bar_pt2_1_prt_2 - -> Seq Scan on bar_pt2_1_prt_3 - -> Seq Scan on bar_pt2_1_prt_4 - -> Seq Scan on bar_pt2_1_prt_5 - -> Seq Scan on bar_pt2_1_prt_6 + -> Seq Scan on bar_pt2_1_prt_1 bar_pt2_1 + -> Seq Scan on bar_pt2_1_prt_2 bar_pt2_2 + -> Seq Scan on bar_pt2_1_prt_3 bar_pt2_3 + -> Seq Scan on bar_pt2_1_prt_4 bar_pt2_4 + -> Seq Scan on bar_pt2_1_prt_5 bar_pt2_5 + -> Seq Scan on bar_pt2_1_prt_6 bar_pt2_6 -> Hash -> Hash Join - Hash Cond: (bar_pt1_1_prt_1.a1_pc = foo.a) + Hash Cond: (bar_pt1.a1_pc = foo.a) -> Append Partition Selectors: $0 - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Partition Selector (selector id: $0) -> Seq Scan on foo @@ -2408,26 +2408,26 @@ select * from bar_PT1 right join foo on bar_PT1.a1_PC =foo.a right join bar_PT2 -- FOR LIST PARTITIONED TABLE -- Outer table: List Partitioned table, Join Condition on Partition key: Yes, Result: DPE - YES explain (costs off) select * from bar_List_PT1 right join foo on bar_List_PT1.a1_PC =foo.a; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_list_pt1_1_prt_p1.a1_pc = foo.a) + Hash Cond: (bar_list_pt1.a1_pc = foo.a) -> Append Partition Selectors: $0 - -> Seq Scan on bar_list_pt1_1_prt_p1 - -> Seq Scan on bar_list_pt1_1_prt_p2 - -> Seq Scan on bar_list_pt1_1_prt_p3 - -> Seq Scan on bar_list_pt1_1_prt_p4 - -> Seq Scan on bar_list_pt1_1_prt_p5 - -> Seq Scan on bar_list_pt1_1_prt_p6 - -> Seq Scan on bar_list_pt1_1_prt_p7 - -> Seq Scan on bar_list_pt1_1_prt_p8 - -> Seq Scan on bar_list_pt1_1_prt_p9 - -> Seq Scan on bar_list_pt1_1_prt_p10 - -> Seq Scan on bar_list_pt1_1_prt_p11 - -> Seq Scan on bar_list_pt1_1_prt_p12 - -> Seq Scan on bar_list_pt1_1_prt_pdefault + -> Seq Scan on bar_list_pt1_1_prt_p1 bar_list_pt1_1 + -> Seq Scan on bar_list_pt1_1_prt_p2 bar_list_pt1_2 + -> Seq Scan on bar_list_pt1_1_prt_p3 bar_list_pt1_3 + -> Seq Scan on bar_list_pt1_1_prt_p4 bar_list_pt1_4 + -> Seq Scan on bar_list_pt1_1_prt_p5 bar_list_pt1_5 + -> Seq Scan on bar_list_pt1_1_prt_p6 bar_list_pt1_6 + -> Seq Scan on bar_list_pt1_1_prt_p7 bar_list_pt1_7 + -> Seq Scan on bar_list_pt1_1_prt_p8 bar_list_pt1_8 + -> Seq Scan on bar_list_pt1_1_prt_p9 bar_list_pt1_9 + -> Seq Scan on bar_list_pt1_1_prt_p10 bar_list_pt1_10 + -> Seq Scan on bar_list_pt1_1_prt_p11 bar_list_pt1_11 + -> Seq Scan on bar_list_pt1_1_prt_p12 bar_list_pt1_12 + -> Seq Scan on bar_list_pt1_1_prt_pdefault bar_list_pt1_13 -> Hash -> Partition Selector (selector id: $0) -> Seq Scan on foo @@ -2446,36 +2446,36 @@ select * from bar_List_PT1 right join foo on bar_List_PT1.a1_PC =foo.a; -- Outer,Inner table: Partitioned table, Join Condition on Partition key: Yes, Result: DPE - Yes explain (costs off) select * from bar_List_PT1 right join bar_List_PT2 on bar_List_PT1.a1_PC =bar_List_PT2.a2_PC; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_list_pt1_1_prt_p1.a1_pc = bar_list_pt2_1_prt_p1.a2_pc) + Hash Cond: (bar_list_pt1.a1_pc = bar_list_pt2.a2_pc) -> Append Partition Selectors: $0 - -> Seq Scan on bar_list_pt1_1_prt_p1 - -> Seq Scan on bar_list_pt1_1_prt_p2 - -> Seq Scan on bar_list_pt1_1_prt_p3 - -> Seq Scan on bar_list_pt1_1_prt_p4 - -> Seq Scan on bar_list_pt1_1_prt_p5 - -> Seq Scan on bar_list_pt1_1_prt_p6 - -> Seq Scan on bar_list_pt1_1_prt_p7 - -> Seq Scan on bar_list_pt1_1_prt_p8 - -> Seq Scan on bar_list_pt1_1_prt_p9 - -> Seq Scan on bar_list_pt1_1_prt_p10 - -> Seq Scan on bar_list_pt1_1_prt_p11 - -> Seq Scan on bar_list_pt1_1_prt_p12 - -> Seq Scan on bar_list_pt1_1_prt_pdefault + -> Seq Scan on bar_list_pt1_1_prt_p1 bar_list_pt1_1 + -> Seq Scan on bar_list_pt1_1_prt_p2 bar_list_pt1_2 + -> Seq Scan on bar_list_pt1_1_prt_p3 bar_list_pt1_3 + -> Seq Scan on bar_list_pt1_1_prt_p4 bar_list_pt1_4 + -> Seq Scan on bar_list_pt1_1_prt_p5 bar_list_pt1_5 + -> Seq Scan on bar_list_pt1_1_prt_p6 bar_list_pt1_6 + -> Seq Scan on bar_list_pt1_1_prt_p7 bar_list_pt1_7 + -> Seq Scan on bar_list_pt1_1_prt_p8 bar_list_pt1_8 + -> Seq Scan on bar_list_pt1_1_prt_p9 bar_list_pt1_9 + -> Seq Scan on bar_list_pt1_1_prt_p10 bar_list_pt1_10 + -> Seq Scan on bar_list_pt1_1_prt_p11 bar_list_pt1_11 + -> Seq Scan on bar_list_pt1_1_prt_p12 bar_list_pt1_12 + -> Seq Scan on bar_list_pt1_1_prt_pdefault bar_list_pt1_13 -> Hash -> Partition Selector (selector id: $0) -> Append - -> Seq Scan on bar_list_pt2_1_prt_p1 - -> Seq Scan on bar_list_pt2_1_prt_p2 - -> Seq Scan on bar_list_pt2_1_prt_p3 - -> Seq Scan on bar_list_pt2_1_prt_p4 - -> Seq Scan on bar_list_pt2_1_prt_p5 - -> Seq Scan on bar_list_pt2_1_prt_p6 - -> Seq Scan on bar_list_pt2_1_prt_pdefault + -> Seq Scan on bar_list_pt2_1_prt_p1 bar_list_pt2_1 + -> Seq Scan on bar_list_pt2_1_prt_p2 bar_list_pt2_2 + -> Seq Scan on bar_list_pt2_1_prt_p3 bar_list_pt2_3 + -> Seq Scan on bar_list_pt2_1_prt_p4 bar_list_pt2_4 + -> Seq Scan on bar_list_pt2_1_prt_p5 bar_list_pt2_5 + -> Seq Scan on bar_list_pt2_1_prt_p6 bar_list_pt2_6 + -> Seq Scan on bar_list_pt2_1_prt_pdefault bar_list_pt2_7 Optimizer: Postgres query optimizer (29 rows) @@ -2499,20 +2499,20 @@ select * from bar_List_PT1 right join bar_List_PT2 on bar_List_PT1.a1_PC =bar_Li -- Case-2 : Distribution colm <> Partition Key. -- Outer table: Partitioned table, Join Condition on Partition key: Yes, Result: DPE - No explain (costs off) select * from bar_PT2 right join foo on bar_PT2.b2_PC =foo.a; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Right Join - Hash Cond: (bar_pt2_1_prt_1.b2_pc = foo.a) + Hash Cond: (bar_pt2.b2_pc = foo.a) -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: bar_pt2_1_prt_1.b2_pc + Hash Key: bar_pt2.b2_pc -> Append - -> Seq Scan on bar_pt2_1_prt_1 - -> Seq Scan on bar_pt2_1_prt_2 - -> Seq Scan on bar_pt2_1_prt_3 - -> Seq Scan on bar_pt2_1_prt_4 - -> Seq Scan on bar_pt2_1_prt_5 - -> Seq Scan on bar_pt2_1_prt_6 + -> Seq Scan on bar_pt2_1_prt_1 bar_pt2_1 + -> Seq Scan on bar_pt2_1_prt_2 bar_pt2_2 + -> Seq Scan on bar_pt2_1_prt_3 bar_pt2_3 + -> Seq Scan on bar_pt2_1_prt_4 bar_pt2_4 + -> Seq Scan on bar_pt2_1_prt_5 bar_pt2_5 + -> Seq Scan on bar_pt2_1_prt_6 bar_pt2_6 -> Hash -> Seq Scan on foo Optimizer: Postgres query optimizer @@ -2530,30 +2530,30 @@ select * from bar_PT2 right join foo on bar_PT2.b2_PC =foo.a; -- Outer,Inner table: Partitioned table, Join Condition on Partition key: Yes, Result: DPE - No explain (costs off) select * from bar_PT2 right join bar_PT1 on bar_PT2.b2_PC =bar_PT1.b1; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Left Join - Hash Cond: (bar_pt1_1_prt_1.b1 = bar_pt2_1_prt_1.b2_pc) + Hash Cond: (bar_pt1.b1 = bar_pt2.b2_pc) -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: bar_pt1_1_prt_1.b1 + Hash Key: bar_pt1.b1 -> Append - -> Seq Scan on bar_pt1_1_prt_1 - -> Seq Scan on bar_pt1_1_prt_2 - -> Seq Scan on bar_pt1_1_prt_3 - -> Seq Scan on bar_pt1_1_prt_4 - -> Seq Scan on bar_pt1_1_prt_5 - -> Seq Scan on bar_pt1_1_prt_6 + -> Seq Scan on bar_pt1_1_prt_1 bar_pt1_1 + -> Seq Scan on bar_pt1_1_prt_2 bar_pt1_2 + -> Seq Scan on bar_pt1_1_prt_3 bar_pt1_3 + -> Seq Scan on bar_pt1_1_prt_4 bar_pt1_4 + -> Seq Scan on bar_pt1_1_prt_5 bar_pt1_5 + -> Seq Scan on bar_pt1_1_prt_6 bar_pt1_6 -> Hash -> Redistribute Motion 3:3 (slice3; segments: 3) - Hash Key: bar_pt2_1_prt_1.b2_pc + Hash Key: bar_pt2.b2_pc -> Append - -> Seq Scan on bar_pt2_1_prt_1 - -> Seq Scan on bar_pt2_1_prt_2 - -> Seq Scan on bar_pt2_1_prt_3 - -> Seq Scan on bar_pt2_1_prt_4 - -> Seq Scan on bar_pt2_1_prt_5 - -> Seq Scan on bar_pt2_1_prt_6 + -> Seq Scan on bar_pt2_1_prt_1 bar_pt2_1 + -> Seq Scan on bar_pt2_1_prt_2 bar_pt2_2 + -> Seq Scan on bar_pt2_1_prt_3 bar_pt2_3 + -> Seq Scan on bar_pt2_1_prt_4 bar_pt2_4 + -> Seq Scan on bar_pt2_1_prt_5 bar_pt2_5 + -> Seq Scan on bar_pt2_1_prt_6 bar_pt2_6 Optimizer: Postgres query optimizer (23 rows) diff --git a/src/test/regress/expected/join_gp_optimizer.out b/src/test/regress/expected/join_gp_optimizer.out index 52248dee78..e1e35cb45b 100644 --- a/src/test/regress/expected/join_gp_optimizer.out +++ b/src/test/regress/expected/join_gp_optimizer.out @@ -1865,7 +1865,7 @@ EXPLAIN select t1.* from t1 left join (t2 left join t3 on t3.a=t2.b) on t2.a=t1. (3 rows) -- inner table is a derived table -EXPLAIN +EXPLAIN (COSTS OFF) select t1.* from t1 left join ( select t2.b as v2b, count(*) as v2c @@ -1873,10 +1873,10 @@ select t1.* from t1 left join group by t2.b ) v2 on v2.v2b=t1.a; - QUERY PLAN ------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=4) - -> Seq Scan on t1 (cost=0.00..431.00 rows=1 width=4) + QUERY PLAN +------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) + -> Seq Scan on t1 Optimizer: Pivotal Optimizer (GPORCA) (3 rows) diff --git a/src/test/regress/expected/join_optimizer.out b/src/test/regress/expected/join_optimizer.out index e2c30b8547..fa62644cda 100644 --- a/src/test/regress/expected/join_optimizer.out +++ b/src/test/regress/expected/join_optimizer.out @@ -5105,22 +5105,12 @@ select d.* from d left join (select id from a union select id from b) s explain (costs off) select i8.* from int8_tbl i8 left join (select f1 from int4_tbl group by f1) i4 on i8.q1 = i4.f1; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Hash Left Join - Hash Cond: (int8_tbl.q1 = (int4_tbl.f1)::bigint) - -> Seq Scan on int8_tbl - -> Hash - -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: (int4_tbl.f1)::bigint - -> GroupAggregate - Group Key: int4_tbl.f1 - -> Sort - Sort Key: int4_tbl.f1 - -> Seq Scan on int4_tbl + -> Seq Scan on int8_tbl Optimizer: Pivotal Optimizer (GPORCA) -(13 rows) +(3 rows) -- check join removal with lateral references explain (costs off) diff --git a/src/test/regress/expected/partition_prune_opfamily.out b/src/test/regress/expected/partition_prune_opfamily.out index 31d4ba33d9..1b4de10322 100644 --- a/src/test/regress/expected/partition_prune_opfamily.out +++ b/src/test/regress/expected/partition_prune_opfamily.out @@ -281,9 +281,9 @@ explain select * from t1 where t1.b |=| 1; ------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..16283.50 rows=86100 width=8) -> Append (cost=0.00..15135.50 rows=28700 width=8) - -> Seq Scan on t1_1_prt_p2 (cost=0.00..7496.00 rows=14350 width=8) + -> Seq Scan on t1_1_prt_p2 t1_1 (cost=0.00..7496.00 rows=14350 width=8) Filter: (b |=| 1) - -> Seq Scan on t1_1_prt_p1 (cost=0.00..7496.00 rows=14350 width=8) + -> Seq Scan on t1_1_prt_p1 t1_2 (cost=0.00..7496.00 rows=14350 width=8) Filter: (b |=| 1) Optimizer: Postgres query optimizer (7 rows) @@ -302,20 +302,20 @@ explain select * from t1, t2 where t1.b = t2.b and t2.b |=| 1; ----------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=2794.50..426963.08 rows=13414380 width=20) -> Hash Join (cost=2794.50..248104.68 rows=4471460 width=20) - Hash Cond: (t2_1_prt_p2.b = t1_1_prt_p2.b) + Hash Cond: (t2.b = t1.b) -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..14219.83 rows=25967 width=12) - Hash Key: t2_1_prt_p2.b + Hash Key: t2.b -> Append (cost=0.00..13700.50 rows=25967 width=12) - -> Seq Scan on t2_1_prt_p2 (cost=0.00..6785.33 rows=12983 width=12) + -> Seq Scan on t2_1_prt_p2 t2_1 (cost=0.00..6785.33 rows=12983 width=12) Filter: (b |=| 1) - -> Seq Scan on t2_1_prt_p1 (cost=0.00..6785.33 rows=12983 width=12) + -> Seq Scan on t2_1_prt_p1 t2_2 (cost=0.00..6785.33 rows=12983 width=12) Filter: (b |=| 1) -> Hash (cost=2077.00..2077.00 rows=57400 width=8) -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..2077.00 rows=57400 width=8) - Hash Key: t1_1_prt_p2.b + Hash Key: t1.b -> Append (cost=0.00..929.00 rows=57400 width=8) - -> Seq Scan on t1_1_prt_p2 (cost=0.00..321.00 rows=28700 width=8) - -> Seq Scan on t1_1_prt_p1 (cost=0.00..321.00 rows=28700 width=8) + -> Seq Scan on t1_1_prt_p2 t1_1 (cost=0.00..321.00 rows=28700 width=8) + -> Seq Scan on t1_1_prt_p1 t1_2 (cost=0.00..321.00 rows=28700 width=8) Optimizer: Postgres query optimizer (17 rows) @@ -333,19 +333,19 @@ select * from t1, t2 where t1.b = t2.b and t2.b |=| 1; (8 rows) explain select * from t1, t2 where t1.a = t2.a and t1.b |=| t2.b; - QUERY PLAN ---------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=1496.17..1344006.67 rows=13414380 width=20) -> Hash Join (cost=1496.17..1165148.27 rows=4471460 width=20) - Hash Cond: (t1_1_prt_p2.a = t2_1_prt_p2.a) - Join Filter: (t1_1_prt_p2.b |=| t2_1_prt_p2.b) + Hash Cond: (t1.a = t2.a) + Join Filter: (t1.b |=| t2.b) -> Append (cost=0.00..929.00 rows=57400 width=8) - -> Seq Scan on t1_1_prt_p2 (cost=0.00..321.00 rows=28700 width=8) - -> Seq Scan on t1_1_prt_p1 (cost=0.00..321.00 rows=28700 width=8) + -> Seq Scan on t1_1_prt_p2 t1_1 (cost=0.00..321.00 rows=28700 width=8) + -> Seq Scan on t1_1_prt_p1 t1_2 (cost=0.00..321.00 rows=28700 width=8) -> Hash (cost=847.00..847.00 rows=51933 width=12) -> Append (cost=0.00..847.00 rows=51933 width=12) - -> Seq Scan on t2_1_prt_p2 (cost=0.00..293.67 rows=25967 width=12) - -> Seq Scan on t2_1_prt_p1 (cost=0.00..293.67 rows=25967 width=12) + -> Seq Scan on t2_1_prt_p2 t2_1 (cost=0.00..293.67 rows=25967 width=12) + -> Seq Scan on t2_1_prt_p1 t2_2 (cost=0.00..293.67 rows=25967 width=12) Optimizer: Postgres query optimizer (12 rows) @@ -363,17 +363,17 @@ select * from t1, t2 where t1.a = t2.a and t1.b |=| t2.b; (8 rows) explain select * from t1, t2 where t1.a = t2.a and t1.b |=| t2.b and t2.b = 1; - QUERY PLAN -------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=358.91..1921.43 rows=6707 width=20) - -> Hash Join (cost=358.91..1832.00 rows=2236 width=20) - Hash Cond: (t1_1_prt_p2.a = t2_1_prt_p1.a) - Join Filter: (t1_1_prt_p2.b |=| t2_1_prt_p1.b) + QUERY PLAN +---------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=358.91..1921.44 rows=6707 width=20) + -> Hash Join (cost=358.91..1832.02 rows=2236 width=20) + Hash Cond: (t1.a = t2.a) + Join Filter: (t1.b |=| t2.b) -> Append (cost=0.00..929.00 rows=57400 width=8) - -> Seq Scan on t1_1_prt_p2 (cost=0.00..321.00 rows=28700 width=8) - -> Seq Scan on t1_1_prt_p1 (cost=0.00..321.00 rows=28700 width=8) + -> Seq Scan on t1_1_prt_p2 t1_1 (cost=0.00..321.00 rows=28700 width=8) + -> Seq Scan on t1_1_prt_p1 t1_2 (cost=0.00..321.00 rows=28700 width=8) -> Hash (cost=358.58..358.58 rows=26 width=12) - -> Seq Scan on t2_1_prt_p1 (cost=0.00..358.58 rows=26 width=12) + -> Seq Scan on t2_1_prt_p1 t2 (cost=0.00..358.58 rows=26 width=12) Filter: (b = 1) Optimizer: Postgres query optimizer (11 rows) diff --git a/src/test/regress/expected/partition_pruning.out b/src/test/regress/expected/partition_pruning.out index b2c44dd8b1..ea727afd4e 100644 --- a/src/test/regress/expected/partition_pruning.out +++ b/src/test/regress/expected/partition_pruning.out @@ -3026,10 +3026,10 @@ partition by list(col2) INSERT INTO pt_bool_tab SELECT i, true FROM generate_series(1,3)i; INSERT INTO pt_bool_tab SELECT i, false FROM generate_series(1,2)i; EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS true; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS TRUE) Optimizer: Postgres query optimizer (4 rows) @@ -3043,10 +3043,10 @@ SELECT * FROM pt_bool_tab WHERE col2 IS true; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS false; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS FALSE) Optimizer: Postgres query optimizer (4 rows) @@ -3072,13 +3072,13 @@ SELECT * FROM pt_bool_tab WHERE col2 IS NULL; (0 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS unknown; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000000694.14 rows=187 width=5) -> Append (cost=10000000000.00..20000000691.65 rows=62 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) Optimizer: Postgres query optimizer (7 rows) @@ -3089,10 +3089,10 @@ SELECT * FROM pt_bool_tab WHERE col2 IS unknown; (0 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS NOT TRUE) Optimizer: Postgres query optimizer (4 rows) @@ -3105,10 +3105,10 @@ SELECT * FROM pt_bool_tab WHERE col2 IS NOT true; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS NOT FALSE) Optimizer: Postgres query optimizer (4 rows) @@ -3122,13 +3122,13 @@ SELECT * FROM pt_bool_tab WHERE col2 IS NOT false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) Optimizer: Postgres query optimizer (7 rows) @@ -3144,13 +3144,13 @@ SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT NULL) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT NULL) Optimizer: Postgres query optimizer (7 rows) @@ -3181,10 +3181,10 @@ INSERT INTO pt_bool_tab_df SELECT i, true FROM generate_series(1,3)i; INSERT INTO pt_bool_tab_df SELECT i, false FROM generate_series(1,2)i; INSERT INTO pt_bool_tab_df SELECT i, NULL FROM generate_series(1,1)i; EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS true; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS TRUE) Optimizer: Postgres query optimizer (4 rows) @@ -3198,10 +3198,10 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS true; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS false; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS FALSE) Optimizer: Postgres query optimizer (4 rows) @@ -3214,10 +3214,10 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS false; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; - QUERY PLAN ---------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000346.91 rows=94 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS NULL) Optimizer: Postgres query optimizer (4 rows) @@ -3229,15 +3229,15 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) Optimizer: Postgres query optimizer (9 rows) @@ -3249,10 +3249,10 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS NOT TRUE) Optimizer: Postgres query optimizer (4 rows) @@ -3265,10 +3265,10 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS NOT FALSE) Optimizer: Postgres query optimizer (4 rows) @@ -3282,15 +3282,15 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) Optimizer: Postgres query optimizer (9 rows) @@ -3306,15 +3306,15 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT NULL) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT NULL) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT NULL) Optimizer: Postgres query optimizer (9 rows) @@ -3345,10 +3345,10 @@ INSERT INTO pt_bool_tab_null SELECT i, true FROM generate_series(1,3)i; INSERT INTO pt_bool_tab_null SELECT i, false FROM generate_series(1,2)i; INSERT INTO pt_bool_tab_null SELECT i, NULL FROM generate_series(1,1)i; EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS true; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS TRUE) Optimizer: Postgres query optimizer (4 rows) @@ -3362,10 +3362,10 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS true; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS false; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS FALSE) Optimizer: Postgres query optimizer (4 rows) @@ -3378,10 +3378,10 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS false; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; - QUERY PLAN -------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000346.91 rows=94 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS NULL) Optimizer: Postgres query optimizer (4 rows) @@ -3393,15 +3393,15 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: (col2 IS UNKNOWN) Optimizer: Postgres query optimizer (9 rows) @@ -3413,10 +3413,10 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS NOT TRUE) Optimizer: Postgres query optimizer (4 rows) @@ -3429,10 +3429,10 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000000969.00 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: (col2 IS NOT FALSE) Optimizer: Postgres query optimizer (4 rows) @@ -3446,15 +3446,15 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT UNKNOWN) Optimizer: Postgres query optimizer (9 rows) @@ -3470,13 +3470,13 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT NULL) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: (col2 IS NOT NULL) Optimizer: Postgres query optimizer (7 rows) diff --git a/src/test/regress/sql/gp_dqa.sql b/src/test/regress/sql/gp_dqa.sql index fcd2408d84..8402b78737 100644 --- a/src/test/regress/sql/gp_dqa.sql +++ b/src/test/regress/sql/gp_dqa.sql @@ -407,6 +407,7 @@ explain (verbose, costs off) select count(distinct (b)::text) as b, count(distin -- column '(a)::integer::varchar' as part of hash-key in Redistribute-Motion. select count(distinct (b)::text) as b, count(distinct (a)::int::varchar) as a from dqa_f3; explain (verbose, costs off) select count(distinct (b)::text) as b, count(distinct (a)::int::varchar) as a from dqa_f3; +drop table dqa_f3; -- Test 3-phase agg for DISTINCT on distribution keys -- or DISTINCT when GROUP BY on distribution keys @@ -497,10 +498,10 @@ explain select id, count(distinct a), avg(b), sum(c) from num_table group by gro select id, count(distinct a), avg(b), sum(c) from num_table group by grouping sets ((id,c)); reset optimizer_force_multistage_agg; -reset optimizer_enable_use_distribution_in_dqa; -drop table t_issue_659; -- DQA with Agg(Intermediate Agg) +-- In PG optimizer Intermediate Agg have not support yet +-- Current test cases will be changed after commit 971fa82(Support intermediate Agg in planner (#13959)) set enable_hashagg=on; set enable_groupagg=off; diff --git a/src/test/regress/sql/join_gp.sql b/src/test/regress/sql/join_gp.sql index 2f2e30c17a..f7584a2b4a 100644 --- a/src/test/regress/sql/join_gp.sql +++ b/src/test/regress/sql/join_gp.sql @@ -815,7 +815,7 @@ EXPLAIN select t1.a from t1 left join (t2 join t3 on true) on t2.a=t1.a and t3.a -- inner table has new left join EXPLAIN select t1.* from t1 left join (t2 left join t3 on t3.a=t2.b) on t2.a=t1.a; -- inner table is a derived table -EXPLAIN +EXPLAIN (COSTS OFF) select t1.* from t1 left join ( select t2.b as v2b, count(*) as v2c --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
