Diagnosing Hash Join Batch Spills #

A hash join builds a table from one input and probes it with the other. While that table fits in memory the join is close to optimal. When it does not, the executor partitions both inputs into batches, writes most of them to temporary files, and processes them one pair at a time — turning a single pass into many, with a full write and read of the spilled data in between.

The plan reports this precisely, in three fields on the Hash node. This page shows how to read them and how to trace a spill back to its actual cause. The operator itself is covered in hash join mechanics.

The Fields That Describe a Spill #

Every Hash node under EXPLAIN ANALYZE reports:

The relationship between Batches and Original Hash Batches is the diagnosis:

Batches Original Meaning
1 — No spill; the build side fit comfortably
8 8 Planned spill: the planner knew it would not fit
32 4 Estimate collapse: the build side was far bigger than expected

The third row is the expensive one. Increasing the batch count mid-build means re-partitioning rows that were already hashed, which is pure re-work on top of the temporary I/O.

How a hash join spills into batches The build input is partitioned into four batches; batch zero stays in memory while batches one to three are written to temporary files. The probe input is partitioned the same way. Each spilled pair is then read back and joined in a later pass. build side (under Hash) 12M rows partition by hash batch 0 batch 1 batch 2 batch 3 in memory ↑ only batch 0 temporary files — written once, read once probe side 48M rows matching batch pairs are joined in later passes — one extra write+read per spilled batch

Annotated Evidence #

An estimate collapse looks like this:

Hash Join  (cost=48210.11..891402.10 rows=4120334 width=96)
           (actual time=18220.4..39810.1 rows=4120334 loops=1)
  Hash Cond: (e.session_id = s.session_id)
  ->  Seq Scan on events e  (actual rows=48000000 loops=1)
  ->  Hash  (cost=28104.00..28104.00 rows=890000 width=48)
            (actual time=18102.6..18102.6 rows=12000000 loops=1)
        Buckets: 65536 (originally 1048576)
        Batches: 32 (originally 4)          -- re-partitioned mid-build
        Memory Usage: 65536kB
        ->  Seq Scan on sessions s  (actual rows=12000000 loops=1)
  Buffers: shared read=182400, temp read=214880 written=214880

Estimated 890,000 build rows against 12,000,000 actual — a 13× underestimate. The executor started with 4 batches, discovered the table would not fit, and escalated to 32, re-hashing what it had already built. The temp read/written pair confirms roughly 1.7 GB of temporary traffic in each direction.

A planned spill is far less dramatic:

  ->  Hash  (actual rows=12000000 loops=1)
        Buckets: 65536  Batches: 16  Memory Usage: 65536kB

No originally clauses. The planner knew the build side was large, chose 16 batches up front, and partitioned cleanly. This is a resource situation, not an estimation bug.

Step-by-Step Resolution #

  1. Read the three fields in order: Batches, any originally value, then the estimated-versus-actual rows on the Hash node’s child.

  2. If the estimate collapsed, repair the statistics rather than the memory:

    ANALYZE sessions;
-- correlated predicates on the build side benefit from extended statistics
CREATE STATISTICS sessions_tenant_type (ndistinct, dependencies)
  ON tenant_id, session_type FROM sessions;
ANALYZE sessions;

  3. Shrink the build side. The hash table holds only the columns the join needs, so removing wide columns from the target list and pushing filters below the Hash node both reduce it — see filter pushdown mechanics.

  4. Check which side is being hashed. PostgreSQL hashes the side it estimates to be smaller; a bad estimate can invert that. When the estimate is corrected, the roles often swap on their own and the spill disappears.

  5. If the estimate was right, size the allowance for the statement:

    BEGIN;
SET LOCAL work_mem = '512MB';          -- hash nodes get 2× by default
SET LOCAL hash_mem_multiplier = 2.0;
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;
COMMIT;

  6. Consider a different strategy for genuinely huge inputs. A merge join over indexed inputs has predictable memory behavior where a hash join does not.

  7. Confirm the fix: Batches: 1, no originally clause, and no temp figures in the buffers line.

Three hash join outcomes compared Bars for three cases: a single-batch join with no temporary IO and the shortest time, a planned sixteen-batch spill with moderate temporary IO, and an estimate collapse to thirty-two batches with the largest temporary IO and time. Batches: 1 8.4 s · no temp I/O Batches: 16 (planned) 21.7 s · temp 900 MB Batches: 32 (was 4) 39.8 s · temp 1.7 GB the gap between rows two and three is re-partitioning work, not data volume

Before and After #

-- BEFORE: build side estimated 890k, actually 12M
Hash  Buckets: 65536 (originally 1048576)  Batches: 32 (originally 4)
      temp read=214880 written=214880
Execution Time: 39810.1 ms

-- AFTER: ANALYZE + extended statistics; planner hashes the correct side
Hash  Buckets: 2097152  Batches: 1  Memory Usage: 412880kB
      (no temp figures)
Execution Time: 8402.6 ms

Batches returning to 1 and the temp counters vanishing is the complete signature of a resolved spill. Execution time is the consequence.

Common Pitfalls #

Raising work_mem when the estimate collapsed. With a 13× underestimate you would need 13× the memory you predicted, on every concurrent backend. Diagnostic signal: an originally clause on Batches. Fix: repair the statistics first.

Reading Memory Usage as the requirement. It reports the resident portion after partitioning, not the size the whole table needed. Diagnostic signal: a modest figure beside large temp written. Fix: size from the actual build-side row count and width.

Forgetting hash_mem_multiplier. Hash nodes get work_mem × hash_mem_multiplier, so sorts and hashes cross their thresholds at different points. Diagnostic signal: sorts clean while hashes still batch. Fix: raise the multiplier.

Ignoring which side is hashed. Hashing the 48-million-row side instead of the 12-million-row side guarantees a spill. Diagnostic signal: the Hash node sitting over the larger relation. Fix: correct the estimates so the planner picks the smaller build side.

Treating a spill as harmless because the query still finishes. Temporary I/O is the part of a plan’s cost that degrades worst under concurrency: twenty backends each writing a gigabyte of batch files contend for the same disk and the same temp_file_limit. Diagnostic signal: a query whose isolated timing looks acceptable but whose latency triples at peak. Fix: eliminate the spill rather than tolerating it, and monitor pg_stat_database.temp_bytes as a workload-level indicator.

Letting a skewed join key defeat partitioning. Batching assumes the hash distributes rows evenly. When one key value accounts for a large share of the build side, its partition stays oversized no matter how many batches the executor creates, and the re-partitioning loop can repeat several times. Diagnostic signal: a high Batches count with Peak Memory Usage still at the allowance ceiling. Fix: check the most-common-value list in pg_stats for the join column, and consider filtering or handling the dominant value separately.

Forgetting that parallel workers each build their own table. In a parallel plan that is not a Parallel Hash Join, every worker builds a private copy of the hash table, so total memory is the allowance multiplied by the participants. Diagnostic signal: memory pressure that scales with Workers Launched. Fix: budget accordingly, or use the shared-hash variant where available.

Each Hash node field answers one question Four fields with the diagnostic question each one answers: Batches asks whether it spilled, Original Hash Batches asks whether the estimate held, Memory Usage asks how much stayed resident, and the child row counts ask whether the right side was hashed. Batches did it spill at all? Batches (originally N) did the estimate hold, or did it re-partition? Memory Usage how much stayed resident (not what was needed) child actual rows was the smaller relation really the build side?

Frequently Asked Questions #

What is the difference between Batches and Original Hash Batches? Original Hash Batches is what the executor planned for; Batches is what it used. A larger final figure means the batch count was increased mid-build, forcing already-hashed rows to be re-partitioned — the most expensive spill variant.

Which side does Memory Usage refer to? The build side, under the Hash node. The probe side streams through and is never held in memory, which is why getting the smaller relation onto the build side is often the cheapest available fix.

Can a hash join spill with a correct estimate? Yes. If the build side genuinely exceeds work_mem × hash_mem_multiplier, the planner spills deliberately and says so by choosing a batch count up front. That is a resource decision, and the fix is a larger allowance, a narrower build side, or a different join method.