Aggregate

An Aggregate node computes aggregate functions. It has four strategies: Plain (one row, no GROUP BY), GroupAggregate (sorted input, groups read in order), HashAggregate (groups built in a hash table), and MixedAggregate (grouping sets combining both).

Plain for un-grouped aggregates; GroupAggregate when the input is already sorted on the grouping keys (e.g. via an index); HashAggregate when hashing the groups is cheaper than sorting.

Normal and necessary. The thing to watch is a HashAggregate whose groups exceed work_mem: since PostgreSQL 13 it spills to disk rather than over-using memory, which is correct but slow.

Four strategies, one node

EXPLAIN prints a different label depending on how the Aggregate runs:

• Aggregate — plain aggregation with no GROUP BY (one output row, e.g. SELECT count(*)).
• GroupAggregate — input is sorted on the grouping keys; groups are read as consecutive runs. Cheap when an index or a prior Sort already provides order.
• HashAggregate — groups are accumulated in a hash table; no sorted input needed. Usually the choice for many distinct groups.
• MixedAggregate — GROUPING SETS / ROLLUP / CUBE, mixing hashed and sorted grouping in one pass.

HashAggregate and spilling

Before PostgreSQL 13, a HashAggregate that out-grew work_mem simply used more memory. Since 13 it spills to disk instead — correct, but slow, and visible as temp-file I/O:

HashAggregate  (actual time=... rows=99307 loops=1)
  Group Key: val
  Planned Partitions: 4  Batches: 388  Memory Usage: 169kB  Disk Usage: 15040kB
  Buffers: shared hit=8329, temp read=5485 written=6707

Batches: 388 and Disk Usage: 15040kB mean the groups didn't fit. The cure:

SET LOCAL work_mem = '128MB';
-- group/hash memory specifically:
SET LOCAL hash_mem_multiplier = 4.0;

GroupAggregate vs HashAggregate

If a useful index exists on the grouping columns, the planner may stream a GroupAggregate off an Index Scan with no sort and no hash table at all — often the cheapest option for high-cardinality GROUP BY. Whether that beats a HashAggregate depends on row counts and work_mem, so keep statistics fresh.

Plain aggregates and indexes

SELECT count(*) FROM t still reads every row (an aggregate over the whole table). SELECT max(col) or min(col), by contrast, can be turned into a tiny Index Scan ("Result" with an InitPlan) when an index on col exists — PostgreSQL just reads one end of the index.

• Touched temp files — a HashAggregate spilled its groups to disk (raise work_mem)

Paste a plan into the analyzer →

work_memhash_mem_multiplierenable_hashagg

How we tune these →

What's the difference between HashAggregate and GroupAggregate?

HashAggregate builds a hash table of groups and needs no sorted input; GroupAggregate requires the input sorted on the grouping columns and reads groups as runs. The planner picks whichever it estimates is cheaper given available indexes and work_mem.

• Group
• Sort
• WindowAgg
• SetOp