Partial and Finalize Aggregate in Parallel Plans #

A GROUP BY customer_id over 400 million rows runs with four workers, but the plan shows Partial HashAggregate producing 380 million rows across the workers and Finalize HashAggregate in the leader doing almost all the real work. The query is barely faster than its serial version. Parallel aggregation only helps when the workers can shrink the data before it reaches the leader.

Aggregation strategies are covered in aggregate and grouping strategies; worker mechanics in parallel query execution. This page covers where the two meet.

The Planner Condition #

Parallel aggregation is a two-phase plan:

  1. Partial Aggregate (or Partial HashAggregate / Partial GroupAggregate) runs in each worker and in the leader, aggregating only the rows that process scanned. It emits a transition state per group — for avg, a running sum and count — not a final value.
  2. Gather or Gather Merge collects those partial states.
  3. Finalize Aggregate (or Finalize HashAggregate / Finalize GroupAggregate) combines states for the same group using the aggregate’s combine function and computes final values.

Requirements:

When it pays: the benefit is proportional to how much each worker’s partial aggregate reduces its input. Grouping 400 million rows into 12 regions, each worker emits 12 small states and the leader combines 60 — near-linear speedup. Grouping into 380 million distinct customers, each worker emits almost one state per row, the Gather must transfer all of them, and the leader’s finalize step does a second full aggregation. The planner estimates this from the expected group count; with a poor estimate it can choose the split when it is useless.

Aggregate in workers, combine in the leader Four stages left to right. Parallel Seq Scan in each worker reads a share of the table. Partial Aggregate in each worker produces transition states per group. Gather transfers the states to the leader. Finalize Aggregate combines states for the same group and computes final values. Parallel Seq Scan share of rows per worker Partial Aggregate states per group Gather states sent to leader Finalize Aggregate combine + final value speedup depends on how much Partial Aggregate shrinks the data

Annotated EXPLAIN Evidence #

Few groups — the split works:

EXPLAIN (ANALYZE)
SELECT region, sum(amount) FROM sales GROUP BY region;
Finalize GroupAggregate  (actual time=9204.1..9204.3 rows=12 loops=1)
  Group Key: region
  ->  Gather Merge  (actual time=9204.0..9204.2 rows=60 loops=1)
        Workers Launched: 4
        ->  Sort  (actual rows=12 loops=5)
              Sort Key: region
              ->  Partial HashAggregate  (actual time=9198.4..9198.5 rows=12 loops=5)
                    Group Key: region
                    ->  Parallel Seq Scan on sales  (actual rows=80008022 loops=5)
Execution Time: 9210.6 ms
-- 400M rows reduced to 12 states per process; leader combines 60 rows

Many groups — the split does not work:

EXPLAIN (ANALYZE)
SELECT customer_id, sum(amount) FROM sales GROUP BY customer_id;
Finalize HashAggregate  (actual time=112804.2..121204.6 rows=380120004 loops=1)
  Group Key: customer_id
  Batches: 129  Memory Usage: 262193kB  Disk Usage: 18104220kB
  ->  Gather  (actual time=4.1..61204.2 rows=392004110 loops=1)
        Workers Launched: 4
        ->  Partial HashAggregate  (actual rows=78400822 loops=5)
              Group Key: customer_id
              Batches: 33  Disk Usage: 3204410kB
              ->  Parallel Seq Scan on sales  (actual rows=80008022 loops=5)
Execution Time: 124102.6 ms
-- workers reduced 80M rows to 78M states each: almost no reduction
-- Gather moved 392M states; the leader re-aggregated them with 18 GB of spill
The reduction ratio decides the benefit Three plan lines are annotated. The Partial HashAggregate row count per loop compared with the scan's rows per loop gives the reduction ratio. The Gather row count shows how many states were transferred. The Finalize node's disk usage shows the leader repeating the aggregation. Parallel Seq Scan rows=80008022 loops=5 input per process Partial HashAggregate rows=78400822 loops=5 98% of input survives Gather rows=392004110 states shipped to the leader Finalize … Disk Usage: 18104220kB leader redoes the grouping compare partial output rows with partial input rows before anything else

Step-by-Step Resolution #

  1. Compute the reduction ratio: Partial … rows ÷ scan rows per loop. Below about 10% the split helps; near 100% it does not.

  2. Check the group estimate on the partial node against actual rows. An underestimate of distinct groups is what led the planner to split.

    SELECT n_distinct FROM pg_stats WHERE tablename = 'sales' AND attname = 'customer_id';

    Correcting it — see overriding n_distinct on large tables — lets the planner cost the finalize step realistically.

  3. Compare with a serial plan for high-cardinality groupings:

    BEGIN;
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE) SELECT customer_id, sum(amount) FROM sales GROUP BY customer_id;
ROLLBACK;

  4. Reduce what flows through Gather by filtering early, aggregating on a narrower key, or pre-aggregating by a coarser column that the report can use.

  5. Check aggregate eligibility when no Partial node appears at all:

    SELECT a.aggfnoid::regproc, a.aggcombinefn, p.proparallel
FROM pg_aggregate a JOIN pg_proc p ON p.oid = a.aggfnoid
WHERE a.aggfnoid::regproc::text IN ('my_percentile', 'string_agg');

    aggcombinefn = '-' or proparallel = 'u' blocks the split.

  6. Size memory for both phases. Each worker’s partial hash table and the leader’s finalize table each use the hash memory budget.

Few groups scale, many groups do not Grouping 400 million rows by region reduces each worker's output to 12 rows, and parallel aggregation is 4.3 times faster than serial. Grouping by customer keeps 98 percent of rows as states, and the parallel plan is only 1.1 times faster than serial. by region: speedup 4.3× faster than serial by customer_id: speedup 1.1× faster than serial same table, same workers; only the number of groups differs

Before and After #

-- BEFORE: parallel two-phase aggregation over 380M groups
Finalize HashAggregate (Disk Usage: 18104220kB) → Gather (392M rows) → Partial HashAggregate   Execution Time: 124102.6 ms

-- AFTER: corrected n_distinct; planner chose parallel scan + single hash aggregate with larger budget
HashAggregate (Batches: 32) → Gather → Parallel Seq Scan                                        Execution Time: 71840.2 ms

What the leader actually does #

The leader participates in the parallel phase by default (parallel_leader_participation = on): it scans and partially aggregates its own share while also running Gather. The finalize step then runs entirely in the leader after all partial states have arrived. That makes the finalize phase a serial bottleneck whose cost grows with the number of states — which is exactly why low-reduction groupings do not scale. Gather Merge with a Finalize GroupAggregate avoids a second hash table by merging sorted state streams, and the planner prefers it when the partial outputs are small enough to sort cheaply per worker, as in the region example. For high-cardinality keys, neither finalize strategy avoids handling hundreds of millions of states in one process; the only real fix is reducing the number of groups or accepting a serial plan with a well-sized hash budget.

Common Pitfalls #

Assuming parallel aggregation always scales. It depends on reduction. Diagnostic signal: partial output rows close to input rows. Fix: serial aggregation or a coarser grouping.

A single non-parallel aggregate blocking the split. One string_agg(… ORDER BY …) or custom aggregate without a combine function prevents it for the whole query. Diagnostic signal: no Partial nodes with an otherwise eligible scan. Fix: move that aggregate into a separate query or add a combine function.

Reading per-loop rows as totals. rows=12 loops=5 is 60 states. Diagnostic signal: finalize input apparently smaller than expected. Fix: multiply by loops.

Budgeting memory for one hash table. Workers and leader each build one. Diagnostic signal: memory spikes during parallel reports. Fix: budget (workers + 1) + 1 hash tables.

Frequently Asked Questions #

What is Finalize Aggregate in EXPLAIN? #

It is the second phase of parallel aggregation, running in the leader. It combines the partial transition states produced by workers for each group and computes the final aggregate values.

Why is there no Partial Aggregate in my parallel plan? #

At least one aggregate in the query is not parallel-safe, lacks a combine function, or uses DISTINCT or ORDER BY in its arguments. Any one of those prevents the two-phase split for the whole query.

Why is my parallel GROUP BY not faster? #

When the grouping key has nearly as many distinct values as rows, workers cannot shrink their output, so all states are transferred to the leader and aggregated again. Parallelism then adds transfer overhead without reducing the serial finalize work.

Up: Aggregate and Grouping Strategies