Hash Spill (Temp Files from Hash Joins)

Overview

Hash joins build an in-memory hash table from the smaller of two relations being joined. When the hash table exceeds work_mem, Postgres splits it into multiple batches and reads both input relations multiple times from disk—a hash spill. Hash batches > 1 in EXPLAIN ANALYZE output indicates a spill occurred.

Hash spills are distinct from sort spills (pg-r08) but share the same temp_blks counters, so EXPLAIN output is needed to distinguish them. The fix follows the same pattern: increase work_mem for the role running heavy joins, or redesign the query to reduce hash input size via predicates or partial indexes.

insightral uses a heuristic (temp_blks_written combined with query text containing 'join') to flag candidates; EXPLAIN confirmation is recommended before tuning.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Per-query hash spill via pg_stat_statements
SELECT
  left(query, 80)              AS query_snippet,
  calls,
  temp_blks_written,
  temp_blks_read,
  mean_exec_time::numeric(10,2) AS mean_ms
FROM pg_stat_statements
WHERE temp_blks_written > 1000
  AND query ILIKE '%join%'
ORDER BY temp_blks_written DESC
LIMIT 20;

-- Also check EXPLAIN for Hash Batches > 1
-- EXPLAIN (ANALYZE, BUFFERS) <your query>;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Increase hash join memory for the session or role
SET work_mem = '256MB';

-- For complex queries joining large sets, consider rewriting as CTEs
-- or using a partial index to reduce the hash input size
CREATE INDEX CONCURRENTLY idx_<table>_<join_col>
  WHERE <filter_condition>
  ON <table> (<join_col>);

Worked example

Common false positives

• Queries that are intentionally full-table hash joins for data migration purposes — expected and acceptable in that context.
• The pg_stat_statements heuristic may flag sort spills on queries that happen to contain the word 'join' in a subquery or CTE name — verify with EXPLAIN ANALYZE.