Aggregate and Grouping Strategies: HashAggregate vs GroupAggregate #

Every GROUP BY, DISTINCT, and windowed rollup in PostgreSQL is executed by one of a small family of aggregate operators, and the operator the planner picks is decided almost entirely by one number: how many distinct groups it thinks the query will produce. This page explains the choice, the fields that expose it in a plan, and the tuning levers that change it.

Aggregation sits directly on top of the access paths covered in the execution plan fundamentals section — an aggregate node is only ever as good as the rows fed into it, so a grouping problem is often a scan or join problem wearing a disguise.

When the Planner Chooses Each Aggregate Strategy #

PostgreSQL has three aggregate shapes, and it costs all viable ones before picking:

• HashAggregate builds an in-memory hash table keyed by the grouping columns and updates each group’s transition state in place. It needs no ordered input, so it can consume the output of a sequential scan directly. Its cost is roughly linear in input rows, plus a memory requirement proportional to the number of distinct groups.
• GroupAggregate consumes input already sorted on the grouping columns and emits a group whenever the key changes. Its memory footprint is a single group’s state regardless of group count, but it requires either an explicit Sort node or an index that supplies the ordering.
• MixedAggregate appears for GROUPING SETS, ROLLUP, and CUBE, where several grouping keys are computed in one pass — hashed for the sets that fit, sorted for the rest.

The decision hinges on the estimated distinct group count. The planner multiplies the n_distinct figures from pg_stats for each grouping column, caps the result at the input cardinality, and asks whether a hash table of that size fits within work_mem × hash_mem_multiplier. If it fits, HashAggregate is usually cheapest because it skips the sort. If it does not, the estimated cost of spilling makes GroupAggregate — with its explicit sort — the winner.

One consequence surprises people: adding an index that provides the grouping order can remove the HashAggregate from a plan entirely, because the sort that made GroupAggregate expensive is suddenly free. The reverse also happens — dropping an index can turn a cheap streaming aggregate into a memory-hungry hash build.

Reading the Aggregate Node in EXPLAIN #

The two aggregate node types report different diagnostic fields, and knowing which fields belong to which node is most of the skill:

-- HashAggregate under memory pressure
HashAggregate  (cost=48120.00..49640.00 rows=152000 width=40)
                (actual time=1980.4..2610.7 rows=1489223 loops=1)
  Group Key: o.customer_id, o.status         -- the grouping columns
  Planned Partitions: 8  Batches: 17         -- >1 batch = the hash table spilled
  Peak Memory Usage: 4145 kB                 -- high-water mark of the hash table
  Disk Usage: 214560 kB                      -- temp files written during the spill
  ->  Seq Scan on orders o  (actual rows=8000000 loops=1)

Read it in this order. Group Key confirms which columns are grouped — a surprise column here usually means an ORM added one. The estimated rows=152000 against the actual rows=1489223 is a ten-fold group-count underestimate, which is the root cause. Batches: 17 with a Disk Usage figure is the symptom: the hash table did not fit and was partitioned to temporary files. Peak Memory Usage tells you what the surviving in-memory portion consumed, not what the whole table would have needed.

A GroupAggregate reports a different set:

GroupAggregate  (cost=1043312.71..1121512.71 rows=1489223 width=40)
                 (actual time=4102.9..5980.1 rows=1489223 loops=1)
  Group Key: o.customer_id, o.status
  ->  Sort  (actual time=4102.8..4890.3 rows=8000000 loops=1)
        Sort Key: o.customer_id, o.status
        Sort Method: external merge  Disk: 372160kB   -- the sort spilled, not the aggregate

Here the aggregate itself has no memory fields at all, because it only ever holds one group’s transition state. Any spill belongs to the Sort beneath it, which is why fixing a slow GroupAggregate almost always means fixing its input — a topic covered in depth under sort and hash node analysis.

Algorithm Internals: One Pass, Two Very Different Shapes #

HashAggregate runs a single pass. For each input row it hashes the grouping columns, looks up the transition state, and calls the aggregate’s transition function — int8_avg_accum for avg(bigint), for example. Nothing is emitted until the input is exhausted, so the node is fully blocking, and its memory grows with the number of groups, never with the number of input rows.

Since PostgreSQL 13 the node is also spill-capable. When the hash table outgrows its allowance mid-build, the executor picks a number of partitions, writes unprocessed rows for the overflow partitions to temporary files, finishes the resident partitions, then re-reads each spilled partition as its own hash build. Every extra pass is a full write plus a full read of that partition’s rows, which is why Batches: 17 is so much worse than Batches: 1.

GroupAggregate streams. Rows arrive in group-key order, the node accumulates until the key changes, emits the finished group, and resets. Memory is constant, output starts flowing before the input is finished, and a LIMIT above it can stop the whole pipeline early — something a hash aggregate can never do.

The before/after below shows the same query after correcting a bad n_distinct estimate with extended statistics:

-- BEFORE: estimate says 152k groups, reality is 1.49M → spill
HashAggregate  (rows=152000)  (actual rows=1489223)  Batches: 17  Disk Usage: 214560kB
  ->  Seq Scan on orders  (actual rows=8000000)
Execution Time: 2681.4 ms

-- AFTER: CREATE STATISTICS gives the real combined distinct count → planner switches shape
GroupAggregate  (rows=1489223)  (actual rows=1489223)
  ->  Index Scan using orders_customer_status_idx on orders  (actual rows=8000000)
Execution Time: 1442.9 ms

Note what changed and what did not. The row counts are identical; the plan shape changed because the estimate changed. This is the recurring lesson of cost estimation models: you rarely tune the executor, you tune the numbers the planner reasons about.

Memory, Spill Thresholds, and Resource Behavior #

The memory allowance for a hash aggregate is not work_mem alone. Since PostgreSQL 13, hash-based nodes get work_mem × hash_mem_multiplier, where the multiplier defaults to 2.0. With work_mem = 64MB a hash aggregate may use roughly 128 MB before it spills, while a sort in the same plan still spills at 64 MB. That asymmetry explains the common confusion of raising work_mem and watching sorts improve while aggregates keep spilling.

Three quantities decide whether you spill:

1. Distinct group count. The dominant term. Doubling groups doubles the hash table.
2. Transition state width. count(*) keeps 8 bytes per group; array_agg, string_agg, jsonb_agg, and percentile_cont keep unbounded state that grows with the rows in each group and can blow the allowance with only a few thousand groups.
3. Concurrency. The allowance is per node, per backend. A plan with two hash aggregates running on 40 connections can claim 80 allowances at once, which is why global increases are dangerous and per-transaction SET LOCAL is the safer lever — see session parameter drift for how those values diverge in production.

I/O behavior follows from the same picture. A single-batch hash aggregate does no writes at all; the only I/O in the subtree belongs to the scan. A spilling one writes each overflow partition once and reads it back once, so a 214 MB Disk Usage figure means roughly 428 MB of temporary traffic on top of the base scan — traffic that competes with every other backend for the same temp_file_limit and the same disk.

Step-by-Step Tuning Workflow #

1. Capture the plan with real numbers. EXPLAIN (ANALYZE, BUFFERS, SETTINGS) on the exact statement, with representative parameters. SETTINGS prints any non-default GUCs so you can tell whether the plan you are looking at is the one production gets.

   EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
   SELECT customer_id, status, count(*), sum(total_cents)
   FROM orders
   GROUP BY customer_id, status;

2. Compare estimated and actual rows on the aggregate node itself. The aggregate’s rows is the group count, not the input count. A ratio worse than about 5× is the signal that the strategy choice was made on bad information.

3. Measure the real distinct count before you change anything:

   SELECT count(*) FROM (SELECT DISTINCT customer_id, status FROM orders) d;
   SELECT attname, n_distinct FROM pg_stats
    WHERE tablename = 'orders' AND attname IN ('customer_id','status');

4. Fix the statistics, not the plan. If per-column n_distinct is stale, run ANALYZE orders. If the columns are correlated — every status value does not occur for every customer_id — teach the planner the combination:

   CREATE STATISTICS orders_cust_status (ndistinct)
     ON customer_id, status FROM orders;
   ANALYZE orders;

5. Size the memory for the honest group count. If a single-batch hash aggregate is genuinely the right shape, give it enough headroom for one transaction only:

   BEGIN;
   SET LOCAL work_mem = '256MB';
   SET LOCAL hash_mem_multiplier = 2.0;   -- hash nodes get 512MB here
   EXPLAIN (ANALYZE) SELECT customer_id, status, count(*) FROM orders GROUP BY 1, 2;
   COMMIT;

6. Or make the sorted path free. When groups number in the millions, stop fighting the hash table and give the planner an index that already delivers the order:

   CREATE INDEX CONCURRENTLY orders_customer_status_idx
     ON orders (customer_id, status);

   Column order matters here for exactly the reasons set out in multicolumn index column ordering — the index only supplies grouping order if its leading columns match the Group Key prefix.

7. Re-measure and confirm the win is where you think it is. Compare Batches, Disk Usage, and total execution time against the baseline, not just wall-clock.

Common Pitfalls #

Reading the aggregate’s rows as an input count. On an aggregate node, rows is the number of groups emitted. Diagnostic signal: an aggregate showing actual rows=1489223 above a scan showing actual rows=8000000 is not an error, it is 1.49 M groups from 8 M rows. Fix: compare the aggregate’s estimate to its own actual, never to the scan’s.

Raising work_mem and expecting hash nodes to follow linearly. Hash aggregates use work_mem × hash_mem_multiplier. Diagnostic signal: sorts stop spilling but Batches on the aggregate stays above 1. Fix: raise hash_mem_multiplier, or raise work_mem far enough that the multiplied allowance covers the table.

Ignoring wide transition states. array_agg and jsonb_agg accumulate every input row into per-group state. Diagnostic signal: a spill with only a few thousand groups and a large Peak Memory Usage. Fix: aggregate to a scalar first, or push the aggregation down so fewer rows reach the node.

Correlated grouping columns. Multiplying independent n_distinct values overestimates groups for correlated columns, pushing the planner off HashAggregate unnecessarily. Diagnostic signal: estimated groups far above actual on a GroupAggregate plan. Fix: CREATE STATISTICS … (ndistinct) on the column set.

DISTINCT used where GROUP BY was meant. SELECT DISTINCT and GROUP BY on the same columns can produce different plan shapes because DISTINCT applies after the target list is computed. Diagnostic signal: a Unique node above a Sort instead of a HashAggregate. Fix: rewrite as GROUP BY and compare both plans.

Aggregating before filtering. A HAVING clause that only references grouping columns can be applied as a WHERE instead, shrinking the input before the hash build. Diagnostic signal: Filter on the aggregate node with a high Rows Removed by Filter. Fix: move the predicate into WHERE so it can be pushed down — see filter pushdown mechanics.

Frequently Asked Questions #

Is HashAggregate always faster than GroupAggregate? No. HashAggregate avoids a sort, so it wins when the number of distinct groups is small enough that the hash table fits in the memory allowance. GroupAggregate wins when the input is already ordered on the grouping columns, because the ordering is then free, and it also wins when the group count is very large, because a spilling hash table costs more than a streaming merge.

What does Batches: 17 mean on a HashAggregate node? It means the hash table exceeded work_mem × hash_mem_multiplier and the executor partitioned the input, writing 16 partitions to temporary files and processing them in later passes. The accompanying Disk Usage figure is the temporary space consumed. One batch means the whole aggregate stayed in memory.

Why does my aggregate show a Sort node when I never wrote ORDER BY? Because GroupAggregate requires input sorted on the grouping columns, so the planner adds the Sort itself. Either supply an index that provides the order, or make the hash path viable again by fixing the group-count estimate or increasing the memory allowance.

Can a parallel plan use HashAggregate? Yes. A parallel plan typically runs Partial HashAggregate in each worker and combines the per-worker tables in a Finalize HashAggregate above the Gather. Each worker gets its own memory allowance, so total memory is roughly the allowance multiplied by the number of participants — see parallel query execution.

Related #

• Execution Plan Fundamentals — section overview: the operator tree, access paths, and join strategies aggregates sit on top of
• GroupAggregate vs HashAggregate Selection — the exact cost comparison the planner performs
• Diagnosing HashAggregate Disk Spills — turning Batches and Disk Usage into a fix
• DISTINCT vs GROUP BY Plan Differences — why two equivalent queries plan differently
• Sort and Hash Node Analysis — sibling coverage of the memory behavior beneath aggregates