Hash Join

A Hash Join builds an in-memory hash table from the smaller (inner) input — shown as a child Hash node — then scans the outer input and probes the hash for matches. Equality joins only.

For equality joins where one side fits (mostly) in work_mem and neither side is conveniently pre-sorted.

Usually the fastest join for large, unsorted inputs. The risk is memory: if the build side exceeds work_mem it spills to disk in multiple 'batches', which is much slower.

How it works under the hood

A Hash Join runs in two phases. In the build phase it reads the smaller input — shown as a child Hash node — and loads it into an in-memory hash table keyed on the join column. In the probe phase it scans the larger input and, for each row, looks up the hash table to find matches. Hash joins handle equality conditions only (a.id = b.id).

It doesn't care about input order and doesn't need indexes, which makes it the go-to join for large, unsorted data sets.

The cost is memory

The hash table must fit in work_mem (scaled by hash_mem_multiplier). If the build side is bigger than that, PostgreSQL partitions the join into batches: it writes parts of both inputs to temporary files and processes them in passes.

Hash Join  (actual time=... rows=500000 loops=1)
  Hash Cond: (t.big_id = b.id)
  ->  Seq Scan on big2 t  (...)
  ->  Hash  (... rows=580000 loops=1)
        Buckets: 65536  Batches: 32  Memory Usage: 4096kB
        ->  Seq Scan on big b  (...)

Batches: 32 (anything above 1) means it spilled. You'll also often see the join node touch temp files. The fix is to let the build side stay in memory:

SET LOCAL work_mem = '256MB';
-- or, only for hash/group memory:
SET LOCAL hash_mem_multiplier = 4.0;

Build the smaller side

The planner normally hashes the input it estimates is smaller. If estimates are wrong it may hash the bigger side and spill needlessly — another reason a stale ANALYZE hurts. Check that the Hash node sits over the genuinely smaller relation.

Parallel hash join

Under a Gather, the build side can become a Parallel Hash: workers cooperatively build one shared hash table, then probe in parallel. It scales large joins well, but the shared table still has to fit in the combined memory budget, so the same work_mem advice applies — per worker.

• Hash build spilled — the hash table didn't fit in work_mem and batched to disk

Paste a plan into the analyzer →

work_memhash_mem_multiplier

How we tune these →

What does 'Batches: N' mean on a hash join?

Batches greater than 1 means the build side overflowed work_mem, so PostgreSQL partitioned it and processed it in passes, writing temp files. Raising work_mem (or hash_mem_multiplier) for the query keeps it to a single in-memory batch.

• Hash
• Nested Loop Join
• Merge Join