ES004: Hash or aggregate spilled to disk

• Signal: a Hash split into more than one batch, or a hashed aggregate that reports several batches or disk usage.
• Evidence: batches (and the planned number), peak memory, data written to disk.
• Action: raise work_mem or hash_mem_multiplier for the statement. The finding estimates the hash table’s full size and names a work_mem that holds it, set with SET LOCAL in the statement’s transaction, with what that may take across the plan’s operations, processes and concurrent sessions. It points to ES002 when the input of the hash was underestimated.

Example

The hash_aggregate_spill scenario of the test corpus, captured on PostgreSQL 16: hash aggregate that spills to disk because work_mem is tiny (PostgreSQL 13 and later).

SELECT customer_id, count(*), sum(amount) FROM orders GROUP BY customer_id;

HashAggregate  (cost=41354.50..47462.68 rows=19904 width=44) (actual time=71.500..192.878 rows=20000 loops=1)
  Output: customer_id, count(*), sum(amount)
  Group Key: orders.customer_id
  Planned Partitions: 4  Batches: 306  Memory Usage: 173kB  Disk Usage: 7448kB
  Buffers: shared hit=2417, temp read=2524 written=3257
  I/O Timings: temp read=4.012 write=8.228
  ->  Seq Scan on public.orders  (cost=0.00..4417.00 rows=200000 width=10) (actual time=0.013..15.582 rows=200000 loops=1)
        Output: id, customer_id, status, created_at, amount, note
        Buffers: shared hit=2417
Settings: work_mem = '64kB', enable_sort = 'off', max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=103
Planning Time: 0.536 ms
Execution Time: 194.424 ms

explainsql reports:

HIGH  ES004 Hash or aggregate spilled to disk
HashAggregate wrote 7.3 MB to disk because its hash table did not fit in work_mem.
Batches: 306
Memory used: 173 kB
Disk used: 7.3 MB
Time in the node: 177.3 ms (91% of the runtime)
→ Raise work_mem (or hash_mem_multiplier) so that the aggregate's hash table fits in memory. For this statement alone: SET LOCAL work_mem = '16MB' in its transaction. Every session that runs the statement at the same time may take that much.

All rules