Skip to content

Commit 47c91b5

Browse files
alvherrealvherre
committed
Restore partition_prune's usage of parallel workers
This reverts commit 4d0f6d3 ("Attempt to stabilize partition_prune test output (2)"), and attempts to stabilize the test by using string replacement to hide any loop count difference in parallel nodes. Discussion: https://postgr.es/m/4475.1523628300@sss.pgh.pa.us
1 parent b15e8f7 commit 47c91b5

File tree

2 files changed

+109
-74
lines changed

2 files changed

+109
-74
lines changed

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

Lines changed: 81 additions & 63 deletions
Original file line numberDiff line numberDiff line change
@@ -1748,16 +1748,34 @@ explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
17481748
(10 rows)
17491749

17501750
-- Parallel append
1751+
-- Suppress the number of loops each parallel node runs for. This is because
1752+
-- more than one worker may run the same parallel node if timing conditions
1753+
-- are just right, which destabilizes the test.
1754+
create function explain_parallel_append(text, int[]) returns setof text
1755+
language plpgsql as
1756+
$$
1757+
declare
1758+
ln text;
1759+
args text := string_agg(u::text, ', ') from unnest($2) u;
1760+
begin
1761+
for ln in
1762+
execute format('explain (analyze, costs off, summary off, timing off) execute %s(%s)',
1763+
$1, args)
1764+
loop
1765+
if ln like '%Parallel%' then
1766+
ln := regexp_replace(ln, 'loops=\d*', 'loops=N');
1767+
end if;
1768+
return next ln;
1769+
end loop;
1770+
end;
1771+
$$;
17511772
prepare ab_q4 (int, int) as
17521773
select avg(a) from ab where a between $1 and $2 and b < 4;
17531774
-- Encourage use of parallel plans
17541775
set parallel_setup_cost = 0;
17551776
set parallel_tuple_cost = 0;
17561777
set min_parallel_table_scan_size = 0;
1757-
-- set this so we get a parallel plan
17581778
set max_parallel_workers_per_gather = 2;
1759-
-- and zero this so that workers don't destabilize the explain output
1760-
set max_parallel_workers = 0;
17611779
-- Execute query 5 times to allow choose_custom_plan
17621780
-- to start considering a generic plan.
17631781
execute ab_q4 (1, 8);
@@ -1790,21 +1808,21 @@ execute ab_q4 (1, 8);
17901808

17911809
(1 row)
17921810

1793-
explain (analyze, costs off, summary off, timing off) execute ab_q4 (2, 2);
1794-
QUERY PLAN
1811+
select explain_parallel_append('ab_q4', '{2, 2}');
1812+
explain_parallel_append
17951813
-------------------------------------------------------------------------------
17961814
Finalize Aggregate (actual rows=1 loops=1)
1797-
-> Gather (actual rows=1 loops=1)
1815+
-> Gather (actual rows=3 loops=1)
17981816
Workers Planned: 2
1799-
Workers Launched: 0
1800-
-> Partial Aggregate (actual rows=1 loops=1)
1801-
-> Parallel Append (actual rows=0 loops=1)
1817+
Workers Launched: 2
1818+
-> Partial Aggregate (actual rows=1 loops=3)
1819+
-> Parallel Append (actual rows=0 loops=N)
18021820
Subplans Removed: 6
1803-
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
1821+
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
18041822
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
1805-
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
1823+
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
18061824
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
1807-
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
1825+
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
18081826
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
18091827
(13 rows)
18101828

@@ -1843,59 +1861,59 @@ execute ab_q5 (1, 2, 3);
18431861

18441862
(1 row)
18451863

1846-
explain (analyze, costs off, summary off, timing off) execute ab_q5 (1, 1, 1);
1847-
QUERY PLAN
1864+
select explain_parallel_append('ab_q5', '{1, 1, 1}');
1865+
explain_parallel_append
18481866
-------------------------------------------------------------------------------
18491867
Finalize Aggregate (actual rows=1 loops=1)
1850-
-> Gather (actual rows=1 loops=1)
1868+
-> Gather (actual rows=3 loops=1)
18511869
Workers Planned: 2
1852-
Workers Launched: 0
1853-
-> Partial Aggregate (actual rows=1 loops=1)
1854-
-> Parallel Append (actual rows=0 loops=1)
1870+
Workers Launched: 2
1871+
-> Partial Aggregate (actual rows=1 loops=3)
1872+
-> Parallel Append (actual rows=0 loops=N)
18551873
Subplans Removed: 6
1856-
-> Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
1874+
-> Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=N)
18571875
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
1858-
-> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
1876+
-> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
18591877
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
1860-
-> Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
1878+
-> Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=N)
18611879
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
18621880
(13 rows)
18631881

1864-
explain (analyze, costs off, summary off, timing off) execute ab_q5 (2, 3, 3);
1865-
QUERY PLAN
1882+
select explain_parallel_append('ab_q5', '{2, 3, 3}');
1883+
explain_parallel_append
18661884
-------------------------------------------------------------------------------
18671885
Finalize Aggregate (actual rows=1 loops=1)
1868-
-> Gather (actual rows=1 loops=1)
1886+
-> Gather (actual rows=3 loops=1)
18691887
Workers Planned: 2
1870-
Workers Launched: 0
1871-
-> Partial Aggregate (actual rows=1 loops=1)
1872-
-> Parallel Append (actual rows=0 loops=1)
1888+
Workers Launched: 2
1889+
-> Partial Aggregate (actual rows=1 loops=3)
1890+
-> Parallel Append (actual rows=0 loops=N)
18731891
Subplans Removed: 3
1874-
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
1892+
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
18751893
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
1876-
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
1894+
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
18771895
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
1878-
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
1896+
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
18791897
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
1880-
-> Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
1898+
-> Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=N)
18811899
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
1882-
-> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
1900+
-> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
18831901
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
1884-
-> Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=1)
1902+
-> Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=N)
18851903
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
18861904
(19 rows)
18871905

18881906
-- Try some params whose values do not belong to any partition.
18891907
-- We'll still get a single subplan in this case, but it should not be scanned.
1890-
explain (analyze, costs off, summary off, timing off) execute ab_q5 (33, 44, 55);
1891-
QUERY PLAN
1908+
select explain_parallel_append('ab_q5', '{33, 44, 55}');
1909+
explain_parallel_append
18921910
-------------------------------------------------------------------------------
18931911
Finalize Aggregate (actual rows=1 loops=1)
1894-
-> Gather (actual rows=1 loops=1)
1912+
-> Gather (actual rows=3 loops=1)
18951913
Workers Planned: 2
1896-
Workers Launched: 0
1897-
-> Partial Aggregate (actual rows=1 loops=1)
1898-
-> Parallel Append (actual rows=0 loops=1)
1914+
Workers Launched: 2
1915+
-> Partial Aggregate (actual rows=1 loops=3)
1916+
-> Parallel Append (actual rows=0 loops=N)
18991917
Subplans Removed: 8
19001918
-> Parallel Seq Scan on ab_a1_b1 (never executed)
19011919
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
@@ -1951,16 +1969,16 @@ execute ab_q6 (1, 2, 3);
19511969

19521970
(1 row)
19531971

1954-
explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
1955-
QUERY PLAN
1972+
select explain_parallel_append('ab_q6', '{0, 0, 1}');
1973+
explain_parallel_append
19561974
---------------------------------------------------------------------------------------------------
19571975
Finalize Aggregate (actual rows=1 loops=1)
1958-
-> Gather (actual rows=1 loops=1)
1976+
-> Gather (actual rows=2 loops=1)
19591977
Workers Planned: 1
1960-
Workers Launched: 0
1961-
-> Partial Aggregate (actual rows=1 loops=1)
1962-
-> Nested Loop (actual rows=0 loops=1)
1963-
-> Parallel Seq Scan on lprt_a a (actual rows=102 loops=1)
1978+
Workers Launched: 1
1979+
-> Partial Aggregate (actual rows=1 loops=2)
1980+
-> Nested Loop (actual rows=0 loops=2)
1981+
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=N)
19641982
Filter: (a = ANY ('{0,0,1}'::integer[]))
19651983
-> Append (actual rows=0 loops=102)
19661984
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
@@ -1988,12 +2006,12 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 3);
19882006
QUERY PLAN
19892007
---------------------------------------------------------------------------------------------------
19902008
Finalize Aggregate (actual rows=1 loops=1)
1991-
-> Gather (actual rows=1 loops=1)
2009+
-> Gather (actual rows=2 loops=1)
19922010
Workers Planned: 1
1993-
Workers Launched: 0
1994-
-> Partial Aggregate (actual rows=1 loops=1)
1995-
-> Nested Loop (actual rows=0 loops=1)
1996-
-> Parallel Seq Scan on lprt_a a (actual rows=104 loops=1)
2011+
Workers Launched: 1
2012+
-> Partial Aggregate (actual rows=1 loops=2)
2013+
-> Nested Loop (actual rows=0 loops=2)
2014+
-> Parallel Seq Scan on lprt_a a (actual rows=52 loops=2)
19972015
Filter: (a = ANY ('{1,0,3}'::integer[]))
19982016
-> Append (actual rows=0 loops=104)
19992017
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
@@ -2020,14 +2038,14 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
20202038
QUERY PLAN
20212039
---------------------------------------------------------------------------------------------------
20222040
Finalize Aggregate (actual rows=1 loops=1)
2023-
-> Gather (actual rows=1 loops=1)
2041+
-> Gather (actual rows=2 loops=1)
20242042
Workers Planned: 1
2025-
Workers Launched: 0
2026-
-> Partial Aggregate (actual rows=1 loops=1)
2027-
-> Nested Loop (actual rows=0 loops=1)
2028-
-> Parallel Seq Scan on lprt_a a (actual rows=102 loops=1)
2043+
Workers Launched: 1
2044+
-> Partial Aggregate (actual rows=1 loops=2)
2045+
-> Nested Loop (actual rows=0 loops=2)
2046+
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
20292047
Filter: (a = ANY ('{1,0,0}'::integer[]))
2030-
Rows Removed by Filter: 2
2048+
Rows Removed by Filter: 1
20312049
-> Append (actual rows=0 loops=102)
20322050
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
20332051
Index Cond: (a = a.a)
@@ -2054,14 +2072,14 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
20542072
QUERY PLAN
20552073
--------------------------------------------------------------------------------------------
20562074
Finalize Aggregate (actual rows=1 loops=1)
2057-
-> Gather (actual rows=1 loops=1)
2075+
-> Gather (actual rows=2 loops=1)
20582076
Workers Planned: 1
2059-
Workers Launched: 0
2060-
-> Partial Aggregate (actual rows=1 loops=1)
2061-
-> Nested Loop (actual rows=0 loops=1)
2062-
-> Parallel Seq Scan on lprt_a a (actual rows=100 loops=1)
2077+
Workers Launched: 1
2078+
-> Partial Aggregate (actual rows=1 loops=2)
2079+
-> Nested Loop (actual rows=0 loops=2)
2080+
-> Parallel Seq Scan on lprt_a a (actual rows=50 loops=2)
20632081
Filter: (a = ANY ('{1,0,0}'::integer[]))
2064-
Rows Removed by Filter: 2
2082+
Rows Removed by Filter: 1
20652083
-> Append (actual rows=0 loops=100)
20662084
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
20672085
Index Cond: (a = a.a)

‎src/test/regress/sql/partition_prune.sql

Lines changed: 28 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -360,29 +360,46 @@ execute ab_q3 (1, 8);
360360
explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
361361

362362
-- Parallel append
363+
364+
-- Suppress the number of loops each parallel node runs for. This is because
365+
-- more than one worker may run the same parallel node if timing conditions
366+
-- are just right, which destabilizes the test.
367+
create function explain_parallel_append(text, int[]) returns setof text
368+
language plpgsql as
369+
$$
370+
declare
371+
ln text;
372+
args text := string_agg(u::text, ', ') from unnest($2) u;
373+
begin
374+
for ln in
375+
execute format('explain (analyze, costs off, summary off, timing off) execute %s(%s)',
376+
$1, args)
377+
loop
378+
if ln like '%Parallel%' then
379+
ln := regexp_replace(ln, 'loops=\d*', 'loops=N');
380+
end if;
381+
return next ln;
382+
end loop;
383+
end;
384+
$$;
385+
363386
prepare ab_q4 (int, int) as
364387
select avg(a) from ab where a between $1 and $2 and b < 4;
365388

366389
-- Encourage use of parallel plans
367390
set parallel_setup_cost = 0;
368391
set parallel_tuple_cost = 0;
369392
set min_parallel_table_scan_size = 0;
370-
371-
-- set this so we get a parallel plan
372393
set max_parallel_workers_per_gather = 2;
373394

374-
-- and zero this so that workers don't destabilize the explain output
375-
set max_parallel_workers = 0;
376-
377395
-- Execute query 5 times to allow choose_custom_plan
378396
-- to start considering a generic plan.
379397
execute ab_q4 (1, 8);
380398
execute ab_q4 (1, 8);
381399
execute ab_q4 (1, 8);
382400
execute ab_q4 (1, 8);
383401
execute ab_q4 (1, 8);
384-
385-
explain (analyze, costs off, summary off, timing off) execute ab_q4 (2, 2);
402+
select explain_parallel_append('ab_q4', '{2, 2}');
386403

387404
-- Test run-time pruning with IN lists.
388405
prepare ab_q5 (int, int, int) as
@@ -396,12 +413,12 @@ execute ab_q5 (1, 2, 3);
396413
execute ab_q5 (1, 2, 3);
397414
execute ab_q5 (1, 2, 3);
398415

399-
explain (analyze, costs off, summary off, timing off) execute ab_q5 (1, 1, 1);
400-
explain (analyze, costs off, summary off, timing off) execute ab_q5 (2, 3, 3);
416+
select explain_parallel_append('ab_q5', '{1, 1, 1}');
417+
select explain_parallel_append('ab_q5', '{2, 3, 3}');
401418

402419
-- Try some params whose values do not belong to any partition.
403420
-- We'll still get a single subplan in this case, but it should not be scanned.
404-
explain (analyze, costs off, summary off, timing off) execute ab_q5 (33, 44, 55);
421+
select explain_parallel_append('ab_q5', '{33, 44, 55}');
405422

406423
-- Test parallel Append with IN list and parameterized nested loops
407424
create table lprt_a (a int not null);
@@ -434,7 +451,7 @@ execute ab_q6 (1, 2, 3);
434451
execute ab_q6 (1, 2, 3);
435452
execute ab_q6 (1, 2, 3);
436453

437-
explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
454+
select explain_parallel_append('ab_q6', '{0, 0, 1}');
438455

439456
insert into lprt_a values(3),(3);
440457

0 commit comments

Comments
 (0)