Merge Join

A Merge Join walks two inputs that are both ordered on the join key, advancing through them in lockstep like a zipper. Inputs arrive sorted either from an index or from explicit Sort nodes.

For equality (and some range) joins on large inputs that are already sorted on the join key — e.g. both sides read via a matching index.

Great when the sort order is free (from indexes). Less attractive when the planner has to sort both sides first — a Hash Join is usually cheaper then, unless the sorted output is also needed upstream.

How it works under the hood

A Merge Join requires both inputs sorted on the join key. It then advances through them together, like merging two sorted lists: read from whichever side is "behind," emit matches when the keys line up. Each input is read essentially once, so for very large, already-sorted relations it can beat a hash join.

The "already sorted" part is everything. The order can come for free from an index (an Index Scan that returns rows in key order) — or PostgreSQL has to add a Sort under each side first.

The cost: sorting both sides

Merge Join  (actual time=... rows=500000 loops=1)
  Merge Cond: (t.big_id = b.id)
  ->  Sort  (... Sort Method: external merge  Disk: 6824kB)
        Sort Key: b.id
        ->  Seq Scan on big b
  ->  Sort  (... Sort Method: external merge  Disk: 7848kB)
        Sort Key: t.big_id
        ->  Seq Scan on big2 t

When you see a Sort feeding both sides — especially ones that spill to disk (external merge ... Disk:) — the merge join is paying a steep setup cost. A Hash Join is usually cheaper in that situation. PostgreSQL chose merge here only because hashing was disabled; normally the cost model would prefer the hash.

When merge join is the right call

• Both sides arrive pre-sorted from matching indexes (no Sort nodes) — then it's extremely efficient and streams results in order.
• The query needs the join output in that sort order anyway (e.g. a downstream ORDER BY or merge-based aggregation), so the sort isn't wasted.
• Range/inequality merge conditions that a hash join can't express.

Tuning levers

• work_mem so any required sorts stay in memory instead of spilling.
• The right multi-column index can supply the order for free and remove the Sort nodes entirely.
• If the planner picks merge but a hash would clearly be cheaper, check your row estimates (ANALYZE) — a mis-estimate can tip the cost comparison.

• Merge Join sorts both sides — both inputs needed sorting; a hash join may be cheaper
• Sort spilled to disk — a feeding Sort exceeded work_mem

Paste a plan into the analyzer →

work_memenable_mergejoin

How we tune these →

• Hash Join
• Nested Loop Join
• Sort
• Incremental Sort