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

Commit47c91b5

Browse files
committed
Restore partition_prune's usage of parallel workers
This reverts commit4d0f6d3 ("Attempt to stabilize partition_prunetest output (2)"), and attempts to stabilize the test by using stringreplacement to hide any loop count difference in parallel nodes.Discussion:https://postgr.es/m/4475.1523628300@sss.pgh.pa.us
1 parentb15e8f7 commit47c91b5

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+
createfunctionexplain_parallel_append(text,int[]) returns setoftext
368+
language plpgsqlas
369+
$$
370+
declare
371+
lntext;
372+
argstext := string_agg(u::text,',')from unnest($2) u;
373+
begin
374+
for lnin
375+
execute format('explain (analyze, costs off, summary off, timing off) execute %s(%s)',
376+
$1, args)
377+
loop
378+
if lnlike'%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
selectavg(a)from abwhere a between $1and $2and 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
createtablelprt_a (aintnot 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_avalues(3),(3);
440457

0 commit comments

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