diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 82b68e7..de54db4 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1990,26 +1990,6 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh rollback; drop table list_part; -- Parallel append --- Suppress the number of loops each parallel node runs for. This is because --- more than one worker may run the same parallel node if timing conditions --- are just right, which destabilizes the test. -create function explain_parallel_append(text) returns setof text -language plpgsql as -$$ -declare - ln text; -begin - for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', - $1) - loop - if ln like '%Parallel%' then - ln := regexp_replace(ln, 'loops=\d*', 'loops=N'); - end if; - return next ln; - end loop; -end; -$$; prepare ab_q4 (int, int) as select avg(a) from ab where a between $1 and $2 and b < 4; -- Encourage use of parallel plans @@ -2049,23 +2029,29 @@ execute ab_q4 (1, 8); (1 row) -select explain_parallel_append('execute ab_q4 (2, 2)'); - explain_parallel_append -------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=3 loops=1) +explain (costs off) +execute ab_q4 (2, 2); + QUERY PLAN +------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Workers Planned: 2 - Workers Launched: 2 - -> Partial Aggregate (actual rows=1 loops=3) - -> Parallel Append (actual rows=0 loops=N) + -> Partial Aggregate + -> Parallel Append Subplans Removed: 6 - -> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a2_b1 Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) - -> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a2_b2 Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) - -> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a2_b3 Filter: ((a >= $1) AND (a <= $2) AND (b < 4)) -(13 rows) +(12 rows) + +execute ab_q4 (2, 2); + avg +----- + +(1 row) -- Test run-time pruning with IN lists. prepare ab_q5 (int, int, int) as @@ -2102,85 +2088,109 @@ execute ab_q5 (1, 2, 3); (1 row) -select explain_parallel_append('execute ab_q5 (1, 1, 1)'); - explain_parallel_append +explain (costs off) +execute ab_q5 (1, 1, 1); + QUERY PLAN ------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=3 loops=1) + Finalize Aggregate + -> Gather Workers Planned: 2 - Workers Launched: 2 - -> Partial Aggregate (actual rows=1 loops=3) - -> Parallel Append (actual rows=0 loops=N) + -> Partial Aggregate + -> Parallel Append Subplans Removed: 6 - -> Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a1_b1 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a1_b2 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a1_b3 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) -(13 rows) +(12 rows) + +execute ab_q5 (1, 1, 1); + avg +----- + +(1 row) -select explain_parallel_append('execute ab_q5 (2, 3, 3)'); - explain_parallel_append +explain (costs off) +execute ab_q5 (2, 3, 3); + QUERY PLAN ------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=3 loops=1) + Finalize Aggregate + -> Gather Workers Planned: 2 - Workers Launched: 2 - -> Partial Aggregate (actual rows=1 loops=3) - -> Parallel Append (actual rows=0 loops=N) + -> Partial Aggregate + -> Parallel Append Subplans Removed: 3 - -> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a2_b1 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a2_b2 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a2_b3 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a3_b1 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a3_b2 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a3_b3 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) -(19 rows) +(18 rows) + +execute ab_q5 (2, 3, 3); + avg +----- + +(1 row) -- Try some params whose values do not belong to any partition. -- We'll still get a single subplan in this case, but it should not be scanned. -select explain_parallel_append('execute ab_q5 (33, 44, 55)'); - explain_parallel_append +explain (costs off) +execute ab_q5 (33, 44, 55); + QUERY PLAN ------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=3 loops=1) + Finalize Aggregate + -> Gather Workers Planned: 2 - Workers Launched: 2 - -> Partial Aggregate (actual rows=1 loops=3) - -> Parallel Append (actual rows=0 loops=N) + -> Partial Aggregate + -> Parallel Append Subplans Removed: 8 - -> Parallel Seq Scan on ab_a1_b1 (never executed) + -> Parallel Seq Scan on ab_a1_b1 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) -(9 rows) +(8 rows) + +execute ab_q5 (33, 44, 55); + avg +----- + +(1 row) -- Test Parallel Append with PARAM_EXEC Params -select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2'); - explain_parallel_append -------------------------------------------------------------------------- - Aggregate (actual rows=1 loops=1) +explain (costs off) +select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2; + QUERY PLAN +------------------------------------------------------------------ + Aggregate InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) + -> Result InitPlan 2 (returns $1) - -> Result (actual rows=1 loops=1) - -> Gather (actual rows=0 loops=1) + -> Result + -> Gather Workers Planned: 2 Params Evaluated: $0, $1 - Workers Launched: 2 - -> Parallel Append (actual rows=0 loops=N) - -> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N) + -> Parallel Append + -> Parallel Seq Scan on ab_a1_b2 Filter: ((b = 2) AND ((a = $0) OR (a = $1))) - -> Parallel Seq Scan on ab_a2_b2 (never executed) + -> Parallel Seq Scan on ab_a2_b2 Filter: ((b = 2) AND ((a = $0) OR (a = $1))) - -> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N) + -> Parallel Seq Scan on ab_a3_b2 Filter: ((b = 2) AND ((a = $0) OR (a = $1))) -(16 rows) +(15 rows) + +select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2; + count +------- + 0 +(1 row) -- Test pruning during parallel nested loop query create table lprt_a (a int not null); @@ -2200,171 +2210,199 @@ create index ab_a3_b2_a_idx on ab_a3_b2 (a); create index ab_a3_b3_a_idx on ab_a3_b3 (a); set enable_hashjoin = 0; set enable_mergejoin = 0; -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)'); - explain_parallel_append ---------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=2 loops=1) +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1); + QUERY PLAN +--------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Workers Planned: 1 - Workers Launched: 1 - -> Partial Aggregate (actual rows=1 loops=2) - -> Nested Loop (actual rows=0 loops=2) - -> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N) + -> Partial Aggregate + -> Nested Loop + -> Parallel Seq Scan on lprt_a a Filter: (a = ANY ('{0,0,1}'::integer[])) - -> Append (actual rows=0 loops=102) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2) + -> Append + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 Index Cond: (a = a.a) -(27 rows) +(26 rows) + +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1); + avg +----- + +(1 row) -- Ensure the same partitions are pruned when we make the nested loop -- parameter an Expr rather than a plain Param. -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1)'); - explain_parallel_append ---------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=2 loops=1) +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1); + QUERY PLAN +--------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Workers Planned: 1 - Workers Launched: 1 - -> Partial Aggregate (actual rows=1 loops=2) - -> Nested Loop (actual rows=0 loops=2) - -> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N) + -> Partial Aggregate + -> Nested Loop + -> Parallel Seq Scan on lprt_a a Filter: (a = ANY ('{0,0,1}'::integer[])) - -> Append (actual rows=0 loops=102) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2) + -> Append + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 Index Cond: (a = (a.a + 0)) -(27 rows) +(26 rows) + +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1); + avg +----- + +(1 row) insert into lprt_a values(3),(3); -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)'); - explain_parallel_append ---------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=2 loops=1) +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3); + QUERY PLAN +--------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Workers Planned: 1 - Workers Launched: 1 - -> Partial Aggregate (actual rows=1 loops=2) - -> Nested Loop (actual rows=0 loops=2) - -> Parallel Seq Scan on lprt_a a (actual rows=52 loops=N) + -> Partial Aggregate + -> Nested Loop + -> Parallel Seq Scan on lprt_a a Filter: (a = ANY ('{1,0,3}'::integer[])) - -> Append (actual rows=0 loops=104) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2) + -> Append + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 Index Cond: (a = a.a) -(27 rows) +(26 rows) -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); - explain_parallel_append ---------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=2 loops=1) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3); + avg +----- + +(1 row) + +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); + QUERY PLAN +--------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Workers Planned: 1 - Workers Launched: 1 - -> Partial Aggregate (actual rows=1 loops=2) - -> Nested Loop (actual rows=0 loops=2) - -> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N) + -> Partial Aggregate + -> Nested Loop + -> Parallel Seq Scan on lprt_a a Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: 1 - -> Append (actual rows=0 loops=102) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2) + -> Append + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 Index Cond: (a = a.a) -(28 rows) +(26 rows) + +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); + avg +----- + +(1 row) delete from lprt_a where a = 1; -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); - explain_parallel_append --------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=1 loops=1) - -> Gather (actual rows=2 loops=1) +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); + QUERY PLAN +--------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Workers Planned: 1 - Workers Launched: 1 - -> Partial Aggregate (actual rows=1 loops=2) - -> Nested Loop (actual rows=0 loops=2) - -> Parallel Seq Scan on lprt_a a (actual rows=50 loops=N) + -> Partial Aggregate + -> Nested Loop + -> Parallel Seq Scan on lprt_a a Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: 1 - -> Append (actual rows=0 loops=100) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed) + -> Append + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (never executed) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed) + -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed) + -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed) + -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed) + -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed) + -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed) + -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 Index Cond: (a = a.a) -(28 rows) +(26 rows) + +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); + avg +----- + +(1 row) reset enable_hashjoin; reset enable_mergejoin; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 446af3b..c30cba2 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -451,27 +451,6 @@ drop table list_part; -- Parallel append --- Suppress the number of loops each parallel node runs for. This is because --- more than one worker may run the same parallel node if timing conditions --- are just right, which destabilizes the test. -create function explain_parallel_append(text) returns setof text -language plpgsql as -$$ -declare - ln text; -begin - for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', - $1) - loop - if ln like '%Parallel%' then - ln := regexp_replace(ln, 'loops=\d*', 'loops=N'); - end if; - return next ln; - end loop; -end; -$$; - prepare ab_q4 (int, int) as select avg(a) from ab where a between $1 and $2 and b < 4; @@ -488,7 +467,9 @@ execute ab_q4 (1, 8); execute ab_q4 (1, 8); execute ab_q4 (1, 8); execute ab_q4 (1, 8); -select explain_parallel_append('execute ab_q4 (2, 2)'); +explain (costs off) +execute ab_q4 (2, 2); +execute ab_q4 (2, 2); -- Test run-time pruning with IN lists. prepare ab_q5 (int, int, int) as @@ -502,15 +483,24 @@ execute ab_q5 (1, 2, 3); execute ab_q5 (1, 2, 3); execute ab_q5 (1, 2, 3); -select explain_parallel_append('execute ab_q5 (1, 1, 1)'); -select explain_parallel_append('execute ab_q5 (2, 3, 3)'); +explain (costs off) +execute ab_q5 (1, 1, 1); +execute ab_q5 (1, 1, 1); + +explain (costs off) +execute ab_q5 (2, 3, 3); +execute ab_q5 (2, 3, 3); -- Try some params whose values do not belong to any partition. -- We'll still get a single subplan in this case, but it should not be scanned. -select explain_parallel_append('execute ab_q5 (33, 44, 55)'); +explain (costs off) +execute ab_q5 (33, 44, 55); +execute ab_q5 (33, 44, 55); -- Test Parallel Append with PARAM_EXEC Params -select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2'); +explain (costs off) +select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2; +select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2; -- Test pruning during parallel nested loop query create table lprt_a (a int not null); @@ -535,20 +525,31 @@ create index ab_a3_b3_a_idx on ab_a3_b3 (a); set enable_hashjoin = 0; set enable_mergejoin = 0; -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)'); +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1); +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1); -- Ensure the same partitions are pruned when we make the nested loop -- parameter an Expr rather than a plain Param. -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1)'); +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1); +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1); insert into lprt_a values(3),(3); -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)'); -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3); +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3); + +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); delete from lprt_a where a = 1; -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); +explain (costs off) +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); +select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0); reset enable_hashjoin; reset enable_mergejoin;