0

I have seen in some query plans that the parent is a Finalize GroupAggregate but its child nodes are Partial HashAggregates. When does this make sense?

As an example, I have a query that resembles something like:

=# SELECT x, count(*) AS n FROM t GROUP BY x ;

There is no sorting involved, so why does it choose a GroupAggregate at the top? And why are the parallel workers using HashAggregate?

"Finalize GroupAggregate  (cost=44630.76..47219.48 rows=10218 width=24) (actual time=270.025..309.145 rows=27909 loops=1)"
"  Group Key: x"
"  ->  Gather Merge  (cost=44630.76..47015.12 rows=20436 width=24) (actual time=270.014..293.964 rows=61056 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Sort  (cost=43630.73..43656.28 rows=10218 width=24) (actual time=264.612..270.608 rows=20352 loops=3)"
"              Sort Key: x"
"              Sort Method: external merge  Disk: 728kB"
"              Worker 0:  Sort Method: external merge  Disk: 720kB"
"              Worker 1:  Sort Method: external merge  Disk: 776kB"
"              ->  Partial HashAggregate  (cost=39474.60..42950.27 rows=10218 width=24) (actual time=198.285..223.757 rows=20352 loops=3)"
"                    Group Key: x"
"                    Batches: 5  Memory Usage: 1073kB  Disk Usage: 2312kB"
"                    Worker 0:  Batches: 5  Memory Usage: 1073kB  Disk Usage: 1760kB"
"                    Worker 1:  Batches: 5  Memory Usage: 1073kB  Disk Usage: 3400kB"
"                    ->  Parallel Seq Scan on t (cost=0.00..17344.46 rows=345446 width=16) (actual time=0.053..52.217 rows=276357 loops=3)"

I also saw something similar in this question. Although, in this case, I don't know the original query.

0

2 Answers 2

1

The parallel subplans can potentially return duplicates (in relation to the group key): since the blocks coming out of the Parallel Seq Scan are not distributed between the parallel workers by the group key value, they both can end up processing a certain value of x. The order-preserving merge (Gather Merge) does not eliminate those duplicates, so the final GroupAggregate is necessary to ensure distinct groups.

ELI5 version: Suppose you and your friend want to count how many Skittles® of each colour are in the bag. Each of you take turns to reach into the bag and fetch one Skittle at a time, then you sort them into your own piles by colour and count them; you each probably end up with a small red pile, small green pile, etc. Once you're done, you still have to aggregate your individual results to arrive at the final count.

0
0

As the other answer explains, the individual child workers have duplicates between them. The parent needs to remove these duplicates. Both hashing and sorting are valid methods to remove duplicates.

the parent is a Finalize GroupAggregate but its child nodes are Partial HashAggregates. When does this make sense

In this case, the working memory is not high. So, for the parent node, hash aggregate is less efficient than group aggregate. The planner estimated that sorting and then group aggregating is cheaper than hashing with insufficient memory.

In this example, increasing the amount of working memory leads to the parent also using hash aggregate, like the child node.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.