Hash Spill (Temp Files from Hash Joins)
pg_views: pg_stat_statements, pg_stat_database
Full detection logic, thresholds, and guided fixes ship in the product.
Book a demo →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.
See this rule in action
Full detection logic, thresholds, and guided fixes ship in the product. The licensed agent runs this check — and all others — continuously against your database, surfaces findings with AI explanations, and surfaces a guided remediation path inside the dashboard.
Book a demo →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.
Hash Spill (Temp Files from Hash Joins) — and 100+ more checks — in the licensed product
Full detection logic, thresholds, and guided fixes ship in the product.
Book a demo →Last updated: 2026-05-13 · View the rule library