ES003: Sort spilled to disk

• Signal: a Sort or Incremental Sort, in the leader or in a parallel worker, whose method is external merge or external sort.
• Evidence: sort method, disk space used, sort key, and the time in the node.
• Action: raise work_mem for the statement rather than for the server. The finding names a value: a power of two megabytes, about three times the space written to disk, set with SET LOCAL in the statement’s transaction. It also says what that value may take: each sort, hash and other operation that uses work_mem may take that much, in each process that runs it, in every session that runs the statement at the same time. Alternatively, avoid the sort with an index that matches the sort order.
• Stays silent when: the spill is under 10 MB and the sort takes less than 5% of the runtime.

Example

The sort_external_merge scenario of the test corpus, captured on PostgreSQL 16: sort that spills to disk because work_mem is tiny.

SELECT id, note FROM orders ORDER BY note;

Sort  (cost=38438.14..38938.14 rows=200000 width=37) (actual time=340.230..372.433 rows=200000 loops=1)
  Output: id, note
  Sort Key: orders.note
  Sort Method: external merge  Disk: 9272kB
  Buffers: shared hit=2130 read=290, temp read=4609 written=4921
  I/O Timings: shared read=1.647, temp read=7.540 write=8.700
  ->  Seq Scan on public.orders  (cost=0.00..4417.00 rows=200000 width=37) (actual time=0.014..22.127 rows=200000 loops=1)
        Output: id, note
        Buffers: shared hit=2127 read=290
        I/O Timings: shared read=1.647
Settings: work_mem = '64kB', max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=128
Planning Time: 0.319 ms
Execution Time: 379.494 ms

explainsql reports:

HIGH  ES003 Sort spilled to disk
Sort spilled 9.1 MB to disk.
Sort method: external merge
Disk used: 9.1 MB
Sort key: orders.note
Time in the node: 350.3 ms (92% of the runtime)
→ Raise work_mem so that the sort fits in memory. For this statement alone: SET LOCAL work_mem = '32MB' in its transaction. Every session that runs the statement at the same time may take that much. Or avoid the sort with an index that returns rows ordered by orders.note.

All rules