HashAggregate in PostgreSQL EXPLAIN

What it does

HashAggregate reads its input row by row. For each row it computes the group key (the GROUP BY columns), looks the group up in a hash table, and updates that group’s running aggregates: adds one to the count, adds the total to the sum. When the input ends, it returns one row per group, in no particular order.

It’s used for GROUP BY, for SELECT DISTINCT and UNION (a group with no aggregates is a distinct value), and for some IN (subquery) joins.

Memory grows with the number of groups, not the number of input rows. The limit is work_mem × hash_mem_multiplier: 8 MB with the defaults of PostgreSQL 15 and later, 4 MB on 13 and 14. Since PostgreSQL 13, when the table outgrows that, groups that don’t fit are written to temporary files in partitions and processed afterwards, one batch at a time. (Before 13 it kept growing in memory.)

In parallel plans you’ll see Partial HashAggregate in each process and a Finalize HashAggregate or Finalize GroupAggregate in the leader.

When the planner picks it

• The input isn’t sorted by the group key, and sorting it would cost more than hashing.
• The estimated number of groups fits in memory, or the planner judges spilling acceptable.
• Few groups relative to rows: GROUP BY country over 50,000 customers needs 20 hash entries.

When the input is already sorted on the group key (an index, a merge join), or there are huge numbers of groups, GroupAggregate is the alternative.

Reading its numbers

HashAggregate  (cost=2004.03..2368.45 rows=29154 width=44) (actual time=24.875..43.225 rows=43200.00 loops=1)
  Group Key: customer_id
  Batches: 5  Memory Usage: 8241kB  Disk Usage: 752kB
  Buffers: shared hit=482, temp read=88 written=158

• Group Key: the grouping columns or expressions.
• rows: the number of groups. Estimated 29,154 here, actually 43,200.
• Batches: 1 means everything fitted in memory. More means it spilled and processed the overflow in further passes.
• Memory Usage: peak memory of the hash table. When it spilled, this sits at about the limit (8,241 kB against 8 MB).
• Disk Usage: temporary file space used by the spilled partitions.
• Planned Partitions: appears when the planner expected to spill and planned for it. If you see Batches above 1 without it, the spill came as a surprise, usually from an underestimated group count.
• Buffers: temp read/written: the temporary file traffic.

When it’s a problem

1. It spills. Batches above 1 and a Disk Usage figure. Raise work_mem for the session or the query (SET LOCAL work_mem = '32MB' inside a transaction); in the example, 16 MB brought it back to Batches: 1. Or raise hash_mem_multiplier to give hash tables more room without enlarging sorts. Avoid big server-wide values: every hash in every session can use that much.
2. The group estimate is wrong. The planner estimates groups from each column’s number of distinct values (n_distinct in pg_stats). If it’s far off, ANALYZE, raise the column’s statistics target, or set it directly with ALTER TABLE … ALTER COLUMN … SET (n_distinct = …) and analyse again. For several grouping columns that are related, CREATE STATISTICS … (ndistinct) helps.
3. Too many groups for any sensible memory. Grouping 100 million rows by a near-unique key builds a near-100-million-entry table. An index on the group key lets a GroupAggregate stream the groups with almost no memory, at the cost of reading in index order.
4. Selecting wide columns as group keys. Every key is stored in the table. Group by an id and join the descriptive columns afterwards.

Example

PostgreSQL 18.6, default settings (work_mem 4 MB, hash_mem_multiplier 2):

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i, 'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE customers, orders;

Few groups, all in memory:

EXPLAIN (ANALYZE, BUFFERS) SELECT country, count(*) FROM customers GROUP BY country;

HashAggregate  (cost=1118.00..1118.20 rows=20 width=11) (actual time=9.265..9.268 rows=20.00 loops=1)
  Group Key: country
  Batches: 1  Memory Usage: 32kB
  Buffers: shared hit=368
  ->  Seq Scan on customers  (cost=0.00..868.00 rows=50000 width=3) (actual time=0.021..2.440 rows=50000.00 loops=1)
        Buffers: shared hit=368
Planning:
  Buffers: shared hit=27
Planning Time: 0.183 ms
Execution Time: 9.302 ms

Many groups: June’s orders per customer. Every June order is from a different customer, so 43,200 orders make 43,200 groups:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*), sum(total)
FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01'
GROUP BY customer_id;

HashAggregate  (cost=2004.03..2368.45 rows=29154 width=44) (actual time=24.875..43.225 rows=43200.00 loops=1)
  Group Key: customer_id
  Batches: 5  Memory Usage: 8241kB  Disk Usage: 752kB
  Buffers: shared hit=482, temp read=88 written=158
  ->  Index Scan using orders_created_at_idx on orders  (cost=0.42..1684.23 rows=42640 width=10) (actual time=0.156..10.343 rows=43200.00 loops=1)
        Index Cond: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-07-01 00:00:00+00'::timestamp with time zone))
        Index Searches: 1
        Buffers: shared hit=482
Planning:
  Buffers: shared hit=129
Planning Time: 0.866 ms
Execution Time: 44.963 ms

The planner expected 29,154 groups and no spill (no Planned Partitions); 43,200 arrived and it spilled into 5 batches. With SET work_mem = '16MB':

HashAggregate  (cost=2004.03..2368.45 rows=29154 width=44) (actual time=18.153..31.944 rows=43200.00 loops=1)
  Group Key: customer_id
  Batches: 1  Memory Usage: 17425kB
  Buffers: shared hit=482
  …
Execution Time: 33.581 ms

One batch, 17 MB, 34 ms instead of 45. With work_mem = '256kB' the planner gave up on hashing and sorted instead (GroupAggregate over a Sort … external merge).

PostgreSQL 14 against 18

The same June query with each version’s defaults. PostgreSQL 14.24, where hash_mem_multiplier is 1.0 (a 4 MB limit):

HashAggregate  (cost=4668.76..5469.29 rows=29626 width=44) (actual time=13.805..29.647 rows=43200 loops=1)
  Group Key: customer_id
  Planned Partitions: 4  Batches: 21  Memory Usage: 4281kB  Disk Usage: 1608kB

PostgreSQL 18.6, where it’s 2.0 (an 8 MB limit):

HashAggregate  (cost=1951.92..2308.24 rows=28506 width=44) (actual time=14.635..28.732 rows=43200.00 loops=1)
  Group Key: customer_id
  Batches: 5  Memory Usage: 8241kB  Disk Usage: 752kB

14 planned the spill (Planned Partitions: 4) and needed 21 batches; 18, with twice the memory, five.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts.