Instability of partition_prune regression test results
| От | Tom Lane |
|---|---|
| Тема | Instability of partition_prune regression test results |
| Дата | |
| Msg-id | 11952.1569536725@sss.pgh.pa.us обсуждение исходный текст |
| Ответы |
Re: Instability of partition_prune regression test results
(Amit Langote <amitlangote09@gmail.com>)
|
| Список | pgsql-hackers |
Every so often the partition_prune test falls over, for example here, here, and here: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=sidewinder&dt=2019-08-15%2021%3A45%3A00 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=anole&dt=2019-08-21%2022%3A19%3A23 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=sidewinder&dt=2019-09-11%2018%3A46%3A47 The reason for the failures is quite apparent: we sometimes don't get as many workers as we hoped for. The test script is not quite 100% naive about that, but it's only designed to filter out the "loops" counts of parallel scan nodes. As these examples show, that's utterly inadequate. The "Workers Launched" field is variable too, obviously, and so are the rows and loops counts for every plan node up to the Gather. I experimented with adjusting explain_parallel_append() to filter more fields, but soon realized that we'd have to filter out basically everything that makes it useful to run EXPLAIN ANALYZE at all. Therefore, I think it's time to give up this testing methodology as a bad idea, and fall back to the time-honored way of running a plain EXPLAIN and then the actual query, as per the attached patch. (Note: there's some roughly similar code in select_parallel.sql, but as far as I could find it fails seldom if at all. Likely that is because we don't run select_parallel in parallel with other test scripts. So perhaps an argument could be made to leave partition_prune.sql alone and just run it by itself. I do not care for that answer though, as it will make the regression test suite slower, plus I do not see any argument that this testing method actually provides any info we don't get the traditional way.) BTW, another aspect of this test script that could stand to be nuked from orbit is this method for getting a custom plan: -- Execute query 5 times to allow choose_custom_plan -- to start considering a generic plan. execute ab_q4 (1, 8); execute ab_q4 (1, 8); execute ab_q4 (1, 8); execute ab_q4 (1, 8); execute ab_q4 (1, 8); We should drop that in favor of plan_cache_mode = force_custom_plan, IMO. But I didn't include that change in this patch. regards, tom lane 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;
В списке pgsql-hackers по дате отправления: