From 200c4ed4118f92014253b49faa41a76ecec84c96 Mon Sep 17 00:00:00 2001 From: jcoleman Date: Sat, 22 Jan 2022 18:34:54 -0500 Subject: [PATCH v4 3/4] Changed queries --- src/test/regress/expected/partition_prune.out | 104 +++++++++--------- src/test/regress/expected/select_parallel.out | 26 +++-- 2 files changed, 69 insertions(+), 61 deletions(-) diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7555764c77..5c45f9c0a5 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1284,60 +1284,64 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x; -- -- pruning won't work for mc3p, because some keys are Params explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1; - QUERY PLAN ------------------------------------------------------------------------ - Nested Loop - -> Append - -> Seq Scan on mc2p1 t1_1 - Filter: (a = 1) - -> Seq Scan on mc2p2 t1_2 - Filter: (a = 1) - -> Seq Scan on mc2p_default t1_3 - Filter: (a = 1) - -> Aggregate - -> Append - -> Seq Scan on mc3p0 t2_1 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p1 t2_2 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p2 t2_3 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p3 t2_4 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p4 t2_5 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p5 t2_6 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p6 t2_7 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p7 t2_8 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p_default t2_9 - Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) -(28 rows) + QUERY PLAN +----------------------------------------------------------------------------- + Gather + Workers Planned: 2 + -> Nested Loop + -> Parallel Append + -> Parallel Seq Scan on mc2p1 t1_1 + Filter: (a = 1) + -> Parallel Seq Scan on mc2p2 t1_2 + Filter: (a = 1) + -> Parallel Seq Scan on mc2p_default t1_3 + Filter: (a = 1) + -> Aggregate + -> Append + -> Seq Scan on mc3p0 t2_1 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 t2_2 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p2 t2_3 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p3 t2_4 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p4 t2_5 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p5 t2_6 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p6 t2_7 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p7 t2_8 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p_default t2_9 + Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1)) +(30 rows) -- pruning should work fine, because values for a prefix of keys (a, b) are -- available explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1; - QUERY PLAN ------------------------------------------------------------------------ - Nested Loop - -> Append - -> Seq Scan on mc2p1 t1_1 - Filter: (a = 1) - -> Seq Scan on mc2p2 t1_2 - Filter: (a = 1) - -> Seq Scan on mc2p_default t1_3 - Filter: (a = 1) - -> Aggregate - -> Append - -> Seq Scan on mc3p0 t2_1 - Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p1 t2_2 - Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) - -> Seq Scan on mc3p_default t2_3 - Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) -(16 rows) + QUERY PLAN +----------------------------------------------------------------------------- + Gather + Workers Planned: 2 + -> Nested Loop + -> Parallel Append + -> Parallel Seq Scan on mc2p1 t1_1 + Filter: (a = 1) + -> Parallel Seq Scan on mc2p2 t1_2 + Filter: (a = 1) + -> Parallel Seq Scan on mc2p_default t1_3 + Filter: (a = 1) + -> Aggregate + -> Append + -> Seq Scan on mc3p0 t2_1 + Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 t2_2 + Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p_default t2_3 + Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1)) +(18 rows) -- also here, because values for all keys are provided explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1; diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 124fe9fec5..9bb60c2c1e 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -137,8 +137,8 @@ create table part_pa_test_p2 partition of part_pa_test for values from (0) to (m explain (costs off) select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a))) from part_pa_test pa2; - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------- Aggregate -> Gather Workers Planned: 3 @@ -148,12 +148,14 @@ explain (costs off) SubPlan 2 -> Result SubPlan 1 - -> Append - -> Seq Scan on part_pa_test_p1 pa1_1 - Filter: (a = pa2.a) - -> Seq Scan on part_pa_test_p2 pa1_2 - Filter: (a = pa2.a) -(14 rows) + -> Gather + Workers Planned: 3 + -> Parallel Append + -> Parallel Seq Scan on part_pa_test_p1 pa1_1 + Filter: (a = pa2.a) + -> Parallel Seq Scan on part_pa_test_p2 pa1_2 + Filter: (a = pa2.a) +(16 rows) drop table part_pa_test; -- test with leader participation disabled @@ -1330,8 +1332,10 @@ SELECT 1 FROM tenk1_vw_sec -> Parallel Index Only Scan using tenk1_unique1 on tenk1 SubPlan 1 -> Aggregate - -> Seq Scan on int4_tbl - Filter: (f1 < tenk1_vw_sec.unique1) -(9 rows) + -> Gather + Workers Planned: 1 + -> Parallel Seq Scan on int4_tbl + Filter: (f1 < tenk1_vw_sec.unique1) +(11 rows) rollback; -- 2.17.1