Skip to content

Commit 9de7ea6

Browse files
tglsfdctglsfdc
committed
Improve stability of partition_prune regression test.
This test already knew that, to get stable test output, it had to hide "loops" counts in EXPLAIN ANALYZE results. But that's not nearly enough: if we get a smaller number of workers than we planned for, then the "Workers Launched" number will change, and so will all the rows and loops counts up to the Gather node. This has resulted in repeated failures in the buildfarm, so adjust the test to filter out all these counts. (Really, we wouldn't bother with EXPLAIN ANALYZE at all here, except that currently the only way to verify that executor-time pruning has happened is to look for '(never executed)' annotations. Those are stable and needn't be filtered out.) Back-patch to v11 where the test was introduced. Discussion: https://postgr.es/m/11952.1569536725@sss.pgh.pa.us
1 parent e40f3c7 commit 9de7ea6

File tree

2 files changed

+110
-102
lines changed

2 files changed

+110
-102
lines changed

‎src/test/regress/expected/partition_prune.out

Lines changed: 100 additions & 96 deletions
Original file line numberDiff line numberDiff line change
@@ -1972,9 +1972,14 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
19721972
rollback;
19731973
drop table list_part;
19741974
-- Parallel append
1975-
-- Suppress the number of loops each parallel node runs for. This is because
1976-
-- more than one worker may run the same parallel node if timing conditions
1977-
-- are just right, which destabilizes the test.
1975+
-- Parallel queries won't necessarily get as many workers as the planner
1976+
-- asked for. This affects not only the "Workers Launched:" field of EXPLAIN
1977+
-- results, but also row counts and loop counts for parallel scans, Gathers,
1978+
-- and everything in between. This function filters out the values we can't
1979+
-- rely on to be stable.
1980+
-- This removes enough info that you might wonder why bother with EXPLAIN
1981+
-- ANALYZE at all. The answer is that we need to see '(never executed)'
1982+
-- notations because that's the only way to verify runtime pruning.
19781983
create function explain_parallel_append(text) returns setof text
19791984
language plpgsql as
19801985
$$
@@ -1985,9 +1990,8 @@ begin
19851990
execute format('explain (analyze, costs off, summary off, timing off) %s',
19861991
$1)
19871992
loop
1988-
if ln like '%Parallel%' then
1989-
ln := regexp_replace(ln, 'loops=\d*', 'loops=N');
1990-
end if;
1993+
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
1994+
ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
19911995
return next ln;
19921996
end loop;
19931997
end;
@@ -2034,18 +2038,18 @@ execute ab_q4 (1, 8);
20342038
select explain_parallel_append('execute ab_q4 (2, 2)');
20352039
explain_parallel_append
20362040
-------------------------------------------------------------------------------
2037-
Finalize Aggregate (actual rows=1 loops=1)
2038-
-> Gather (actual rows=3 loops=1)
2041+
Finalize Aggregate (actual rows=N loops=N)
2042+
-> Gather (actual rows=N loops=N)
20392043
Workers Planned: 2
2040-
Workers Launched: 2
2041-
-> Partial Aggregate (actual rows=1 loops=3)
2042-
-> Parallel Append (actual rows=0 loops=N)
2044+
Workers Launched: N
2045+
-> Partial Aggregate (actual rows=N loops=N)
2046+
-> Parallel Append (actual rows=N loops=N)
20432047
Subplans Removed: 6
2044-
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
2048+
-> Parallel Seq Scan on ab_a2_b1 (actual rows=N loops=N)
20452049
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
2046-
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
2050+
-> Parallel Seq Scan on ab_a2_b2 (actual rows=N loops=N)
20472051
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
2048-
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
2052+
-> Parallel Seq Scan on ab_a2_b3 (actual rows=N loops=N)
20492053
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
20502054
(13 rows)
20512055

@@ -2087,42 +2091,42 @@ execute ab_q5 (1, 2, 3);
20872091
select explain_parallel_append('execute ab_q5 (1, 1, 1)');
20882092
explain_parallel_append
20892093
-------------------------------------------------------------------------------
2090-
Finalize Aggregate (actual rows=1 loops=1)
2091-
-> Gather (actual rows=3 loops=1)
2094+
Finalize Aggregate (actual rows=N loops=N)
2095+
-> Gather (actual rows=N loops=N)
20922096
Workers Planned: 2
2093-
Workers Launched: 2
2094-
-> Partial Aggregate (actual rows=1 loops=3)
2095-
-> Parallel Append (actual rows=0 loops=N)
2097+
Workers Launched: N
2098+
-> Partial Aggregate (actual rows=N loops=N)
2099+
-> Parallel Append (actual rows=N loops=N)
20962100
Subplans Removed: 6
2097-
-> Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=N)
2101+
-> Parallel Seq Scan on ab_a1_b1 (actual rows=N loops=N)
20982102
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2099-
-> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
2103+
-> Parallel Seq Scan on ab_a1_b2 (actual rows=N loops=N)
21002104
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2101-
-> Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=N)
2105+
-> Parallel Seq Scan on ab_a1_b3 (actual rows=N loops=N)
21022106
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
21032107
(13 rows)
21042108

21052109
select explain_parallel_append('execute ab_q5 (2, 3, 3)');
21062110
explain_parallel_append
21072111
-------------------------------------------------------------------------------
2108-
Finalize Aggregate (actual rows=1 loops=1)
2109-
-> Gather (actual rows=3 loops=1)
2112+
Finalize Aggregate (actual rows=N loops=N)
2113+
-> Gather (actual rows=N loops=N)
21102114
Workers Planned: 2
2111-
Workers Launched: 2
2112-
-> Partial Aggregate (actual rows=1 loops=3)
2113-
-> Parallel Append (actual rows=0 loops=N)
2115+
Workers Launched: N
2116+
-> Partial Aggregate (actual rows=N loops=N)
2117+
-> Parallel Append (actual rows=N loops=N)
21142118
Subplans Removed: 3
2115-
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
2119+
-> Parallel Seq Scan on ab_a2_b1 (actual rows=N loops=N)
21162120
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2117-
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
2121+
-> Parallel Seq Scan on ab_a2_b2 (actual rows=N loops=N)
21182122
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2119-
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
2123+
-> Parallel Seq Scan on ab_a2_b3 (actual rows=N loops=N)
21202124
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2121-
-> Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=N)
2125+
-> Parallel Seq Scan on ab_a3_b1 (actual rows=N loops=N)
21222126
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2123-
-> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
2127+
-> Parallel Seq Scan on ab_a3_b2 (actual rows=N loops=N)
21242128
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2125-
-> Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=N)
2129+
-> Parallel Seq Scan on ab_a3_b3 (actual rows=N loops=N)
21262130
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
21272131
(19 rows)
21282132

@@ -2131,12 +2135,12 @@ select explain_parallel_append('execute ab_q5 (2, 3, 3)');
21312135
select explain_parallel_append('execute ab_q5 (33, 44, 55)');
21322136
explain_parallel_append
21332137
-------------------------------------------------------------------------------
2134-
Finalize Aggregate (actual rows=1 loops=1)
2135-
-> Gather (actual rows=3 loops=1)
2138+
Finalize Aggregate (actual rows=N loops=N)
2139+
-> Gather (actual rows=N loops=N)
21362140
Workers Planned: 2
2137-
Workers Launched: 2
2138-
-> Partial Aggregate (actual rows=1 loops=3)
2139-
-> Parallel Append (actual rows=0 loops=N)
2141+
Workers Launched: N
2142+
-> Partial Aggregate (actual rows=N loops=N)
2143+
-> Parallel Append (actual rows=N loops=N)
21402144
Subplans Removed: 8
21412145
-> Parallel Seq Scan on ab_a1_b1 (never executed)
21422146
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
@@ -2146,21 +2150,21 @@ select explain_parallel_append('execute ab_q5 (33, 44, 55)');
21462150
select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
21472151
explain_parallel_append
21482152
-------------------------------------------------------------------------
2149-
Aggregate (actual rows=1 loops=1)
2153+
Aggregate (actual rows=N loops=N)
21502154
InitPlan 1 (returns $0)
2151-
-> Result (actual rows=1 loops=1)
2155+
-> Result (actual rows=N loops=N)
21522156
InitPlan 2 (returns $1)
2153-
-> Result (actual rows=1 loops=1)
2154-
-> Gather (actual rows=0 loops=1)
2157+
-> Result (actual rows=N loops=N)
2158+
-> Gather (actual rows=N loops=N)
21552159
Workers Planned: 2
21562160
Params Evaluated: $0, $1
2157-
Workers Launched: 2
2158-
-> Parallel Append (actual rows=0 loops=N)
2159-
-> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
2161+
Workers Launched: N
2162+
-> Parallel Append (actual rows=N loops=N)
2163+
-> Parallel Seq Scan on ab_a1_b2 (actual rows=N loops=N)
21602164
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
21612165
-> Parallel Seq Scan on ab_a2_b2 (never executed)
21622166
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
2163-
-> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
2167+
-> Parallel Seq Scan on ab_a3_b2 (actual rows=N loops=N)
21642168
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
21652169
(16 rows)
21662170

@@ -2185,20 +2189,20 @@ set enable_mergejoin = 0;
21852189
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)');
21862190
explain_parallel_append
21872191
---------------------------------------------------------------------------------------------------
2188-
Finalize Aggregate (actual rows=1 loops=1)
2189-
-> Gather (actual rows=2 loops=1)
2192+
Finalize Aggregate (actual rows=N loops=N)
2193+
-> Gather (actual rows=N loops=N)
21902194
Workers Planned: 1
2191-
Workers Launched: 1
2192-
-> Partial Aggregate (actual rows=1 loops=2)
2193-
-> Nested Loop (actual rows=0 loops=2)
2194-
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N)
2195+
Workers Launched: N
2196+
-> Partial Aggregate (actual rows=N loops=N)
2197+
-> Nested Loop (actual rows=N loops=N)
2198+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
21952199
Filter: (a = ANY ('{0,0,1}'::integer[]))
2196-
-> Append (actual rows=0 loops=102)
2197-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2200+
-> Append (actual rows=N loops=N)
2201+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
21982202
Index Cond: (a = a.a)
2199-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2203+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
22002204
Index Cond: (a = a.a)
2201-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2205+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
22022206
Index Cond: (a = a.a)
22032207
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
22042208
Index Cond: (a = a.a)
@@ -2219,20 +2223,20 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
22192223
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)');
22202224
explain_parallel_append
22212225
---------------------------------------------------------------------------------------------------
2222-
Finalize Aggregate (actual rows=1 loops=1)
2223-
-> Gather (actual rows=2 loops=1)
2226+
Finalize Aggregate (actual rows=N loops=N)
2227+
-> Gather (actual rows=N loops=N)
22242228
Workers Planned: 1
2225-
Workers Launched: 1
2226-
-> Partial Aggregate (actual rows=1 loops=2)
2227-
-> Nested Loop (actual rows=0 loops=2)
2228-
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N)
2229+
Workers Launched: N
2230+
-> Partial Aggregate (actual rows=N loops=N)
2231+
-> Nested Loop (actual rows=N loops=N)
2232+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
22292233
Filter: (a = ANY ('{0,0,1}'::integer[]))
2230-
-> Append (actual rows=0 loops=102)
2231-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2234+
-> Append (actual rows=N loops=N)
2235+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
22322236
Index Cond: (a = (a.a + 0))
2233-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2237+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
22342238
Index Cond: (a = (a.a + 0))
2235-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2239+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
22362240
Index Cond: (a = (a.a + 0))
22372241
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
22382242
Index Cond: (a = (a.a + 0))
@@ -2252,53 +2256,53 @@ insert into lprt_a values(3),(3);
22522256
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)');
22532257
explain_parallel_append
22542258
---------------------------------------------------------------------------------------------------
2255-
Finalize Aggregate (actual rows=1 loops=1)
2256-
-> Gather (actual rows=2 loops=1)
2259+
Finalize Aggregate (actual rows=N loops=N)
2260+
-> Gather (actual rows=N loops=N)
22572261
Workers Planned: 1
2258-
Workers Launched: 1
2259-
-> Partial Aggregate (actual rows=1 loops=2)
2260-
-> Nested Loop (actual rows=0 loops=2)
2261-
-> Parallel Seq Scan on lprt_a a (actual rows=52 loops=N)
2262+
Workers Launched: N
2263+
-> Partial Aggregate (actual rows=N loops=N)
2264+
-> Nested Loop (actual rows=N loops=N)
2265+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
22622266
Filter: (a = ANY ('{1,0,3}'::integer[]))
2263-
-> Append (actual rows=0 loops=104)
2264-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2267+
-> Append (actual rows=N loops=N)
2268+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
22652269
Index Cond: (a = a.a)
2266-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2270+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
22672271
Index Cond: (a = a.a)
2268-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2272+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
22692273
Index Cond: (a = a.a)
22702274
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
22712275
Index Cond: (a = a.a)
22722276
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
22732277
Index Cond: (a = a.a)
22742278
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
22752279
Index Cond: (a = a.a)
2276-
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2)
2280+
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=N loops=N)
22772281
Index Cond: (a = a.a)
2278-
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2)
2282+
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=N loops=N)
22792283
Index Cond: (a = a.a)
2280-
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2)
2284+
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=N loops=N)
22812285
Index Cond: (a = a.a)
22822286
(27 rows)
22832287

22842288
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)');
22852289
explain_parallel_append
22862290
---------------------------------------------------------------------------------------------------
2287-
Finalize Aggregate (actual rows=1 loops=1)
2288-
-> Gather (actual rows=2 loops=1)
2291+
Finalize Aggregate (actual rows=N loops=N)
2292+
-> Gather (actual rows=N loops=N)
22892293
Workers Planned: 1
2290-
Workers Launched: 1
2291-
-> Partial Aggregate (actual rows=1 loops=2)
2292-
-> Nested Loop (actual rows=0 loops=2)
2293-
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N)
2294+
Workers Launched: N
2295+
-> Partial Aggregate (actual rows=N loops=N)
2296+
-> Nested Loop (actual rows=N loops=N)
2297+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
22942298
Filter: (a = ANY ('{1,0,0}'::integer[]))
22952299
Rows Removed by Filter: 1
2296-
-> Append (actual rows=0 loops=102)
2297-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2300+
-> Append (actual rows=N loops=N)
2301+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
22982302
Index Cond: (a = a.a)
2299-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2303+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
23002304
Index Cond: (a = a.a)
2301-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2305+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
23022306
Index Cond: (a = a.a)
23032307
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
23042308
Index Cond: (a = a.a)
@@ -2318,16 +2322,16 @@ delete from lprt_a where a = 1;
23182322
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)');
23192323
explain_parallel_append
23202324
--------------------------------------------------------------------------------------------
2321-
Finalize Aggregate (actual rows=1 loops=1)
2322-
-> Gather (actual rows=2 loops=1)
2325+
Finalize Aggregate (actual rows=N loops=N)
2326+
-> Gather (actual rows=N loops=N)
23232327
Workers Planned: 1
2324-
Workers Launched: 1
2325-
-> Partial Aggregate (actual rows=1 loops=2)
2326-
-> Nested Loop (actual rows=0 loops=2)
2327-
-> Parallel Seq Scan on lprt_a a (actual rows=50 loops=N)
2328+
Workers Launched: N
2329+
-> Partial Aggregate (actual rows=N loops=N)
2330+
-> Nested Loop (actual rows=N loops=N)
2331+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
23282332
Filter: (a = ANY ('{1,0,0}'::integer[]))
23292333
Rows Removed by Filter: 1
2330-
-> Append (actual rows=0 loops=100)
2334+
-> Append (actual rows=N loops=N)
23312335
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
23322336
Index Cond: (a = a.a)
23332337
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed)

0 commit comments

Comments
 (0)