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

Commitc6f28af

Browse files
committed
Avoid generating bogus paths with partitionwise aggregate.
Previously, if some or all partitions had no partially aggregated path,we would still try to generate a partially aggregated path for theparent, leading to assertion failures or wrong answers.Report by Rajkumar Raghuwanshi. Patch by Jeevan Chalke, reviewedby Ashutosh Bapat. A few changes by me.Discussion:http://postgr.es/m/CAKcux6=q4+Mw8gOOX16ef6ZMFp9Cve7KWFstUsrDa4GiFaXGUQ@mail.gmail.com
1 parent2448adf commitc6f28af

File tree

3 files changed

+142
-8
lines changed

3 files changed

+142
-8
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 13 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -7012,6 +7012,7 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
70127012
List*grouped_live_children=NIL;
70137013
List*partially_grouped_live_children=NIL;
70147014
PathTarget*target=grouped_rel->reltarget;
7015+
boolpartial_grouping_valid= true;
70157016

70167017
Assert(patype!=PARTITIONWISE_AGGREGATE_NONE);
70177018
Assert(patype!=PARTITIONWISE_AGGREGATE_PARTIAL||
@@ -7091,6 +7092,8 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
70917092
lappend(partially_grouped_live_children,
70927093
child_partially_grouped_rel);
70937094
}
7095+
else
7096+
partial_grouping_valid= false;
70947097

70957098
if (patype==PARTITIONWISE_AGGREGATE_FULL)
70967099
{
@@ -7102,21 +7105,19 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
71027105
pfree(appinfos);
71037106
}
71047107

7105-
/*
7106-
* All children can't be dummy at this point. If they are, then the parent
7107-
* too marked as dummy.
7108-
*/
7109-
Assert(grouped_live_children!=NIL||
7110-
partially_grouped_live_children!=NIL);
7111-
71127108
/*
71137109
* Try to create append paths for partially grouped children. For full
71147110
* partitionwise aggregation, we might have paths in the partial_pathlist
71157111
* if parallel aggregation is possible. For partial partitionwise
71167112
* aggregation, we may have paths in both pathlist and partial_pathlist.
7113+
*
7114+
* NB: We must have a partially grouped path for every child in order to
7115+
* generate a partially grouped path for this relation.
71177116
*/
7118-
if (partially_grouped_rel)
7117+
if (partially_grouped_rel&&partial_grouping_valid)
71197118
{
7119+
Assert(partially_grouped_live_children!=NIL);
7120+
71207121
add_paths_to_append_rel(root,partially_grouped_rel,
71217122
partially_grouped_live_children);
71227123

@@ -7130,7 +7131,11 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
71307131

71317132
/* If possible, create append paths for fully grouped children. */
71327133
if (patype==PARTITIONWISE_AGGREGATE_FULL)
7134+
{
7135+
Assert(grouped_live_children!=NIL);
7136+
71337137
add_paths_to_append_rel(root,grouped_rel,grouped_live_children);
7138+
}
71347139
}
71357140

71367141
/*

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

Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1394,3 +1394,108 @@ SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) <
13941394
11 | 16500 | 11.0000000000000000 | 1500
13951395
(4 rows)
13961396

1397+
-- Test when parent can produce parallel paths but not any (or some) of its children
1398+
ALTER TABLE pagg_tab_para_p1 SET (parallel_workers = 0);
1399+
ALTER TABLE pagg_tab_para_p3 SET (parallel_workers = 0);
1400+
ANALYZE pagg_tab_para;
1401+
EXPLAIN (COSTS OFF)
1402+
SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1403+
QUERY PLAN
1404+
--------------------------------------------------------------------------------------
1405+
Sort
1406+
Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y))
1407+
-> Finalize GroupAggregate
1408+
Group Key: pagg_tab_para_p1.x
1409+
Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric)
1410+
-> Gather Merge
1411+
Workers Planned: 2
1412+
-> Sort
1413+
Sort Key: pagg_tab_para_p1.x
1414+
-> Partial HashAggregate
1415+
Group Key: pagg_tab_para_p1.x
1416+
-> Parallel Append
1417+
-> Seq Scan on pagg_tab_para_p1
1418+
-> Seq Scan on pagg_tab_para_p3
1419+
-> Parallel Seq Scan on pagg_tab_para_p2
1420+
(15 rows)
1421+
1422+
SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1423+
x | sum | avg | count
1424+
----+------+--------------------+-------
1425+
0 | 5000 | 5.0000000000000000 | 1000
1426+
1 | 6000 | 6.0000000000000000 | 1000
1427+
10 | 5000 | 5.0000000000000000 | 1000
1428+
11 | 6000 | 6.0000000000000000 | 1000
1429+
20 | 5000 | 5.0000000000000000 | 1000
1430+
21 | 6000 | 6.0000000000000000 | 1000
1431+
(6 rows)
1432+
1433+
ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0);
1434+
ANALYZE pagg_tab_para;
1435+
EXPLAIN (COSTS OFF)
1436+
SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1437+
QUERY PLAN
1438+
--------------------------------------------------------------------------------------
1439+
Sort
1440+
Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y))
1441+
-> Finalize GroupAggregate
1442+
Group Key: pagg_tab_para_p1.x
1443+
Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric)
1444+
-> Gather Merge
1445+
Workers Planned: 2
1446+
-> Sort
1447+
Sort Key: pagg_tab_para_p1.x
1448+
-> Partial HashAggregate
1449+
Group Key: pagg_tab_para_p1.x
1450+
-> Parallel Append
1451+
-> Seq Scan on pagg_tab_para_p1
1452+
-> Seq Scan on pagg_tab_para_p2
1453+
-> Seq Scan on pagg_tab_para_p3
1454+
(15 rows)
1455+
1456+
SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1457+
x | sum | avg | count
1458+
----+------+--------------------+-------
1459+
0 | 5000 | 5.0000000000000000 | 1000
1460+
1 | 6000 | 6.0000000000000000 | 1000
1461+
10 | 5000 | 5.0000000000000000 | 1000
1462+
11 | 6000 | 6.0000000000000000 | 1000
1463+
20 | 5000 | 5.0000000000000000 | 1000
1464+
21 | 6000 | 6.0000000000000000 | 1000
1465+
(6 rows)
1466+
1467+
-- Reset parallelism parameters to get partitionwise aggregation plan.
1468+
RESET min_parallel_table_scan_size;
1469+
RESET parallel_setup_cost;
1470+
EXPLAIN (COSTS OFF)
1471+
SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1472+
QUERY PLAN
1473+
--------------------------------------------------------------------------------------
1474+
Sort
1475+
Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y))
1476+
-> Append
1477+
-> HashAggregate
1478+
Group Key: pagg_tab_para_p1.x
1479+
Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric)
1480+
-> Seq Scan on pagg_tab_para_p1
1481+
-> HashAggregate
1482+
Group Key: pagg_tab_para_p2.x
1483+
Filter: (avg(pagg_tab_para_p2.y) < '7'::numeric)
1484+
-> Seq Scan on pagg_tab_para_p2
1485+
-> HashAggregate
1486+
Group Key: pagg_tab_para_p3.x
1487+
Filter: (avg(pagg_tab_para_p3.y) < '7'::numeric)
1488+
-> Seq Scan on pagg_tab_para_p3
1489+
(15 rows)
1490+
1491+
SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3;
1492+
x | sum | avg | count
1493+
----+------+--------------------+-------
1494+
0 | 5000 | 5.0000000000000000 | 1000
1495+
1 | 6000 | 6.0000000000000000 | 1000
1496+
10 | 5000 | 5.0000000000000000 | 1000
1497+
11 | 6000 | 6.0000000000000000 | 1000
1498+
20 | 5000 | 5.0000000000000000 | 1000
1499+
21 | 6000 | 6.0000000000000000 | 1000
1500+
(6 rows)
1501+

‎src/test/regress/sql/partition_aggregate.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -294,3 +294,27 @@ SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) <
294294
EXPLAIN (COSTS OFF)
295295
SELECT y,sum(x),avg(x),count(*)FROM pagg_tab_paraGROUP BY yHAVINGavg(x)<12ORDER BY1,2,3;
296296
SELECT y,sum(x),avg(x),count(*)FROM pagg_tab_paraGROUP BY yHAVINGavg(x)<12ORDER BY1,2,3;
297+
298+
-- Test when parent can produce parallel paths but not any (or some) of its children
299+
ALTERTABLE pagg_tab_para_p1SET (parallel_workers=0);
300+
ALTERTABLE pagg_tab_para_p3SET (parallel_workers=0);
301+
ANALYZE pagg_tab_para;
302+
303+
EXPLAIN (COSTS OFF)
304+
SELECT x,sum(y),avg(y),count(*)FROM pagg_tab_paraGROUP BY xHAVINGavg(y)<7ORDER BY1,2,3;
305+
SELECT x,sum(y),avg(y),count(*)FROM pagg_tab_paraGROUP BY xHAVINGavg(y)<7ORDER BY1,2,3;
306+
307+
ALTERTABLE pagg_tab_para_p2SET (parallel_workers=0);
308+
ANALYZE pagg_tab_para;
309+
310+
EXPLAIN (COSTS OFF)
311+
SELECT x,sum(y),avg(y),count(*)FROM pagg_tab_paraGROUP BY xHAVINGavg(y)<7ORDER BY1,2,3;
312+
SELECT x,sum(y),avg(y),count(*)FROM pagg_tab_paraGROUP BY xHAVINGavg(y)<7ORDER BY1,2,3;
313+
314+
-- Reset parallelism parameters to get partitionwise aggregation plan.
315+
RESET min_parallel_table_scan_size;
316+
RESET parallel_setup_cost;
317+
318+
EXPLAIN (COSTS OFF)
319+
SELECT x,sum(y),avg(y),count(*)FROM pagg_tab_paraGROUP BY xHAVINGavg(y)<7ORDER BY1,2,3;
320+
SELECT x,sum(y),avg(y),count(*)FROM pagg_tab_paraGROUP BY xHAVINGavg(y)<7ORDER BY1,2,3;

0 commit comments

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