work_mem Too Low: Sorts Spilling to Disk

Scenario

A data analyst runs a report query with multiple ORDER BY and GROUP BY clauses. EXPLAIN ANALYZE shows Sort Method: external merge Disk: 450MB. The query takes 8 minutes. Increasing work_mem to 512MB for the session drops runtime to 45 seconds. The global work_mem = 4MB (default) is too small for analytical queries, but setting it globally to 512MB risks OOM because each sort node in each query gets its own work_mem allocation.

How to Identify

Conditions:

• EXPLAIN ANALYZE output contains Sort Method: external merge Disk: XMB
• EXPLAIN ANALYZE contains Batches: N (N > 1) for Hash Join or Hash Aggregate — disk spill
• Slow queries with ORDER BY, GROUP BY, DISTINCT, window functions, hash joins
• pg_stat_statements shows high temp_blks_read and temp_blks_written
• Large temp files in $PGDATA/base/pgsql_tmp/

Analysis Steps

-- Check current work_mem:
SHOW work_mem;

-- Find queries spilling to disk (temp I/O):
SELECT left(query, 80) AS query, calls,
    temp_blks_read, temp_blks_written,
    round(total_exec_time::numeric/calls, 1) AS avg_ms
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

-- Check temp file sizes currently on disk:
SELECT file_name, size
FROM pg_ls_tmpdir()
ORDER BY size DESC
LIMIT 10;

Pitfalls

• work_mem is per sort/hash node per query — a query with 5 sorts and 3 hash joins can use 8 × work_mem simultaneously. Global large work_mem with many connections = OOM.
• Use ALTER ROLE analytics_user SET work_mem = '256MB' to give analytical users more memory without affecting OLTP connections.
• SET work_mem = '512MB' in a session is safe for single analytical queries run interactively.
• maintenance_work_mem (for VACUUM, CREATE INDEX, ALTER TABLE) is separate from work_mem and should be larger (typically 1–2 GB).
• The planner’s decision to sort vs hash depends on work_mem — raising it can change plan shapes (hash join becomes possible).

Resolution Approach

Keep global work_mem low (4–16 MB for OLTP). Grant analytical roles a higher work_mem via ALTER ROLE. For one-off reports, set work_mem at the session level before running.