Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0baa556

Browse files
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 loopscounts up to the Gather node. This has resulted in repeated failures inthe buildfarm, so adjust the test to filter out all these counts.(Really, we wouldn't bother with EXPLAIN ANALYZE at all here, exceptthat currently the only way to verify that executor-time pruning hashappened is to look for '(never executed)' annotations. Those arestable 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 parent240c38a commit0baa556

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
@@ -2013,9 +2013,14 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
20132013
rollback;
20142014
drop table list_part;
20152015
-- Parallel append
2016-
-- Suppress the number of loops each parallel node runs for. This is because
2017-
-- more than one worker may run the same parallel node if timing conditions
2018-
-- are just right, which destabilizes the test.
2016+
-- Parallel queries won't necessarily get as many workers as the planner
2017+
-- asked for. This affects not only the "Workers Launched:" field of EXPLAIN
2018+
-- results, but also row counts and loop counts for parallel scans, Gathers,
2019+
-- and everything in between. This function filters out the values we can't
2020+
-- rely on to be stable.
2021+
-- This removes enough info that you might wonder why bother with EXPLAIN
2022+
-- ANALYZE at all. The answer is that we need to see '(never executed)'
2023+
-- notations because that's the only way to verify runtime pruning.
20192024
create function explain_parallel_append(text) returns setof text
20202025
language plpgsql as
20212026
$$
@@ -2026,9 +2031,8 @@ begin
20262031
execute format('explain (analyze, costs off, summary off, timing off) %s',
20272032
$1)
20282033
loop
2029-
if ln like '%Parallel%' then
2030-
ln := regexp_replace(ln, 'loops=\d*', 'loops=N');
2031-
end if;
2034+
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
2035+
ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
20322036
return next ln;
20332037
end loop;
20342038
end;
@@ -2075,18 +2079,18 @@ execute ab_q4 (1, 8);
20752079
select explain_parallel_append('execute ab_q4 (2, 2)');
20762080
explain_parallel_append
20772081
-------------------------------------------------------------------------------
2078-
Finalize Aggregate (actual rows=1 loops=1)
2079-
-> Gather (actual rows=3 loops=1)
2082+
Finalize Aggregate (actual rows=N loops=N)
2083+
-> Gather (actual rows=N loops=N)
20802084
Workers Planned: 2
2081-
Workers Launched:2
2082-
-> Partial Aggregate (actual rows=1 loops=3)
2083-
-> Parallel Append (actual rows=0 loops=N)
2085+
Workers Launched:N
2086+
-> Partial Aggregate (actual rows=N loops=N)
2087+
-> Parallel Append (actual rows=N loops=N)
20842088
Subplans Removed: 6
2085-
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
2089+
-> Parallel Seq Scan on ab_a2_b1 (actual rows=N loops=N)
20862090
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
2087-
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
2091+
-> Parallel Seq Scan on ab_a2_b2 (actual rows=N loops=N)
20882092
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
2089-
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
2093+
-> Parallel Seq Scan on ab_a2_b3 (actual rows=N loops=N)
20902094
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
20912095
(13 rows)
20922096

@@ -2128,42 +2132,42 @@ execute ab_q5 (1, 2, 3);
21282132
select explain_parallel_append('execute ab_q5 (1, 1, 1)');
21292133
explain_parallel_append
21302134
-------------------------------------------------------------------------------
2131-
Finalize Aggregate (actual rows=1 loops=1)
2132-
-> Gather (actual rows=3 loops=1)
2135+
Finalize Aggregate (actual rows=N loops=N)
2136+
-> Gather (actual rows=N loops=N)
21332137
Workers Planned: 2
2134-
Workers Launched:2
2135-
-> Partial Aggregate (actual rows=1 loops=3)
2136-
-> Parallel Append (actual rows=0 loops=N)
2138+
Workers Launched:N
2139+
-> Partial Aggregate (actual rows=N loops=N)
2140+
-> Parallel Append (actual rows=N loops=N)
21372141
Subplans Removed: 6
2138-
-> Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=N)
2142+
-> Parallel Seq Scan on ab_a1_b1 (actual rows=N loops=N)
21392143
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2140-
-> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
2144+
-> Parallel Seq Scan on ab_a1_b2 (actual rows=N loops=N)
21412145
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2142-
-> Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=N)
2146+
-> Parallel Seq Scan on ab_a1_b3 (actual rows=N loops=N)
21432147
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
21442148
(13 rows)
21452149

21462150
select explain_parallel_append('execute ab_q5 (2, 3, 3)');
21472151
explain_parallel_append
21482152
-------------------------------------------------------------------------------
2149-
Finalize Aggregate (actual rows=1 loops=1)
2150-
-> Gather (actual rows=3 loops=1)
2153+
Finalize Aggregate (actual rows=N loops=N)
2154+
-> Gather (actual rows=N loops=N)
21512155
Workers Planned: 2
2152-
Workers Launched:2
2153-
-> Partial Aggregate (actual rows=1 loops=3)
2154-
-> Parallel Append (actual rows=0 loops=N)
2156+
Workers Launched:N
2157+
-> Partial Aggregate (actual rows=N loops=N)
2158+
-> Parallel Append (actual rows=N loops=N)
21552159
Subplans Removed: 3
2156-
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
2160+
-> Parallel Seq Scan on ab_a2_b1 (actual rows=N loops=N)
21572161
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2158-
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
2162+
-> Parallel Seq Scan on ab_a2_b2 (actual rows=N loops=N)
21592163
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2160-
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
2164+
-> Parallel Seq Scan on ab_a2_b3 (actual rows=N loops=N)
21612165
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2162-
-> Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=N)
2166+
-> Parallel Seq Scan on ab_a3_b1 (actual rows=N loops=N)
21632167
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2164-
-> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
2168+
-> Parallel Seq Scan on ab_a3_b2 (actual rows=N loops=N)
21652169
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2166-
-> Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=N)
2170+
-> Parallel Seq Scan on ab_a3_b3 (actual rows=N loops=N)
21672171
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
21682172
(19 rows)
21692173

@@ -2172,12 +2176,12 @@ select explain_parallel_append('execute ab_q5 (2, 3, 3)');
21722176
select explain_parallel_append('execute ab_q5 (33, 44, 55)');
21732177
explain_parallel_append
21742178
-------------------------------------------------------------------------------
2175-
Finalize Aggregate (actual rows=1 loops=1)
2176-
-> Gather (actual rows=3 loops=1)
2179+
Finalize Aggregate (actual rows=N loops=N)
2180+
-> Gather (actual rows=N loops=N)
21772181
Workers Planned: 2
2178-
Workers Launched:2
2179-
-> Partial Aggregate (actual rows=1 loops=3)
2180-
-> Parallel Append (actual rows=0 loops=N)
2182+
Workers Launched:N
2183+
-> Partial Aggregate (actual rows=N loops=N)
2184+
-> Parallel Append (actual rows=N loops=N)
21812185
Subplans Removed: 8
21822186
-> Parallel Seq Scan on ab_a1_b1 (never executed)
21832187
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
@@ -2187,21 +2191,21 @@ select explain_parallel_append('execute ab_q5 (33, 44, 55)');
21872191
select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
21882192
explain_parallel_append
21892193
-------------------------------------------------------------------------
2190-
Aggregate (actual rows=1 loops=1)
2194+
Aggregate (actual rows=N loops=N)
21912195
InitPlan 1 (returns $0)
2192-
-> Result (actual rows=1 loops=1)
2196+
-> Result (actual rows=N loops=N)
21932197
InitPlan 2 (returns $1)
2194-
-> Result (actual rows=1 loops=1)
2195-
-> Gather (actual rows=0 loops=1)
2198+
-> Result (actual rows=N loops=N)
2199+
-> Gather (actual rows=N loops=N)
21962200
Workers Planned: 2
21972201
Params Evaluated: $0, $1
2198-
Workers Launched:2
2199-
-> Parallel Append (actual rows=0 loops=N)
2200-
-> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
2202+
Workers Launched:N
2203+
-> Parallel Append (actual rows=N loops=N)
2204+
-> Parallel Seq Scan on ab_a1_b2 (actual rows=N loops=N)
22012205
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
22022206
-> Parallel Seq Scan on ab_a2_b2 (never executed)
22032207
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
2204-
-> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
2208+
-> Parallel Seq Scan on ab_a3_b2 (actual rows=N loops=N)
22052209
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
22062210
(16 rows)
22072211

@@ -2226,20 +2230,20 @@ set enable_mergejoin = 0;
22262230
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)');
22272231
explain_parallel_append
22282232
---------------------------------------------------------------------------------------------------
2229-
Finalize Aggregate (actual rows=1 loops=1)
2230-
-> Gather (actual rows=2 loops=1)
2233+
Finalize Aggregate (actual rows=N loops=N)
2234+
-> Gather (actual rows=N loops=N)
22312235
Workers Planned: 1
2232-
Workers Launched:1
2233-
-> Partial Aggregate (actual rows=1 loops=2)
2234-
-> Nested Loop (actual rows=0 loops=2)
2235-
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N)
2236+
Workers Launched:N
2237+
-> Partial Aggregate (actual rows=N loops=N)
2238+
-> Nested Loop (actual rows=N loops=N)
2239+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
22362240
Filter: (a = ANY ('{0,0,1}'::integer[]))
2237-
-> Append (actual rows=0 loops=102)
2238-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2241+
-> Append (actual rows=N loops=N)
2242+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
22392243
Index Cond: (a = a.a)
2240-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2244+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
22412245
Index Cond: (a = a.a)
2242-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2246+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
22432247
Index Cond: (a = a.a)
22442248
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
22452249
Index Cond: (a = a.a)
@@ -2260,20 +2264,20 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
22602264
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)');
22612265
explain_parallel_append
22622266
---------------------------------------------------------------------------------------------------
2263-
Finalize Aggregate (actual rows=1 loops=1)
2264-
-> Gather (actual rows=2 loops=1)
2267+
Finalize Aggregate (actual rows=N loops=N)
2268+
-> Gather (actual rows=N loops=N)
22652269
Workers Planned: 1
2266-
Workers Launched:1
2267-
-> Partial Aggregate (actual rows=1 loops=2)
2268-
-> Nested Loop (actual rows=0 loops=2)
2269-
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N)
2270+
Workers Launched:N
2271+
-> Partial Aggregate (actual rows=N loops=N)
2272+
-> Nested Loop (actual rows=N loops=N)
2273+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
22702274
Filter: (a = ANY ('{0,0,1}'::integer[]))
2271-
-> Append (actual rows=0 loops=102)
2272-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2275+
-> Append (actual rows=N loops=N)
2276+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
22732277
Index Cond: (a = (a.a + 0))
2274-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2278+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
22752279
Index Cond: (a = (a.a + 0))
2276-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2280+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
22772281
Index Cond: (a = (a.a + 0))
22782282
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
22792283
Index Cond: (a = (a.a + 0))
@@ -2293,53 +2297,53 @@ insert into lprt_a values(3),(3);
22932297
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)');
22942298
explain_parallel_append
22952299
---------------------------------------------------------------------------------------------------
2296-
Finalize Aggregate (actual rows=1 loops=1)
2297-
-> Gather (actual rows=2 loops=1)
2300+
Finalize Aggregate (actual rows=N loops=N)
2301+
-> Gather (actual rows=N loops=N)
22982302
Workers Planned: 1
2299-
Workers Launched:1
2300-
-> Partial Aggregate (actual rows=1 loops=2)
2301-
-> Nested Loop (actual rows=0 loops=2)
2302-
-> Parallel Seq Scan on lprt_a a (actual rows=52 loops=N)
2303+
Workers Launched:N
2304+
-> Partial Aggregate (actual rows=N loops=N)
2305+
-> Nested Loop (actual rows=N loops=N)
2306+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
23032307
Filter: (a = ANY ('{1,0,3}'::integer[]))
2304-
-> Append (actual rows=0 loops=104)
2305-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2308+
-> Append (actual rows=N loops=N)
2309+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
23062310
Index Cond: (a = a.a)
2307-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2311+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
23082312
Index Cond: (a = a.a)
2309-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2313+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
23102314
Index Cond: (a = a.a)
23112315
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
23122316
Index Cond: (a = a.a)
23132317
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
23142318
Index Cond: (a = a.a)
23152319
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
23162320
Index Cond: (a = a.a)
2317-
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2)
2321+
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=N loops=N)
23182322
Index Cond: (a = a.a)
2319-
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2)
2323+
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=N loops=N)
23202324
Index Cond: (a = a.a)
2321-
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2)
2325+
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=N loops=N)
23222326
Index Cond: (a = a.a)
23232327
(27 rows)
23242328

23252329
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)');
23262330
explain_parallel_append
23272331
---------------------------------------------------------------------------------------------------
2328-
Finalize Aggregate (actual rows=1 loops=1)
2329-
-> Gather (actual rows=2 loops=1)
2332+
Finalize Aggregate (actual rows=N loops=N)
2333+
-> Gather (actual rows=N loops=N)
23302334
Workers Planned: 1
2331-
Workers Launched:1
2332-
-> Partial Aggregate (actual rows=1 loops=2)
2333-
-> Nested Loop (actual rows=0 loops=2)
2334-
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N)
2335+
Workers Launched:N
2336+
-> Partial Aggregate (actual rows=N loops=N)
2337+
-> Nested Loop (actual rows=N loops=N)
2338+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
23352339
Filter: (a = ANY ('{1,0,0}'::integer[]))
23362340
Rows Removed by Filter: 1
2337-
-> Append (actual rows=0 loops=102)
2338-
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
2341+
-> Append (actual rows=N loops=N)
2342+
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=N loops=N)
23392343
Index Cond: (a = a.a)
2340-
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
2344+
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=N loops=N)
23412345
Index Cond: (a = a.a)
2342-
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
2346+
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=N loops=N)
23432347
Index Cond: (a = a.a)
23442348
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
23452349
Index Cond: (a = a.a)
@@ -2359,16 +2363,16 @@ delete from lprt_a where a = 1;
23592363
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)');
23602364
explain_parallel_append
23612365
--------------------------------------------------------------------------------------------
2362-
Finalize Aggregate (actual rows=1 loops=1)
2363-
-> Gather (actual rows=2 loops=1)
2366+
Finalize Aggregate (actual rows=N loops=N)
2367+
-> Gather (actual rows=N loops=N)
23642368
Workers Planned: 1
2365-
Workers Launched:1
2366-
-> Partial Aggregate (actual rows=1 loops=2)
2367-
-> Nested Loop (actual rows=0 loops=2)
2368-
-> Parallel Seq Scan on lprt_a a (actual rows=50 loops=N)
2369+
Workers Launched:N
2370+
-> Partial Aggregate (actual rows=N loops=N)
2371+
-> Nested Loop (actual rows=N loops=N)
2372+
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
23692373
Filter: (a = ANY ('{1,0,0}'::integer[]))
23702374
Rows Removed by Filter: 1
2371-
-> Append (actual rows=0 loops=100)
2375+
-> Append (actual rows=N loops=N)
23722376
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
23732377
Index Cond: (a = a.a)
23742378
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp