Merge Join Sort Inputs and Materialize Nodes #

A merge join in your plan has a Sort under each input and a Materialize above the inner sort. The join itself looks cheap, but the two sorts spill to disk and the query takes 40 seconds. Another query with the same shape runs in two, because both inputs come from index scans in join-key order and there is nothing to sort.

When a merge join is the right algorithm at all is covered in when merge join beats hash join. This page is about the nodes beneath it, which usually decide whether it is fast.

The Algorithmic Condition #

A merge join walks two inputs in lockstep, both ordered by the join key. It requires:

Sorted inputs. Each side must deliver rows in the order of a mergeable equality operator on the join key. The order can come from an index scan, from a lower merge join, or from an explicit Sort node. A Sort has to consume its entire input before emitting anything, so its cost is paid up front.
Mark and restore on the inner side. When the outer side has several rows with the same key, the join must re-read the matching run of inner rows for each of them. It marks the start of the run and restores to it. Index scans and sorts support this natively. Other inner inputs — a plain sequential scan, a function scan, a subquery — cannot rewind, so the planner inserts a Materialize node that buffers inner rows in memory (up to work_mem, then a temporary file) so they can be revisited.

Sort cost scales as N log N in memory and much worse when it spills, so the attractive merge joins are those where at least one side, ideally both, arrive pre-sorted from an index. The planner also considers incremental sorts when the input is sorted on a prefix of the key, and it can reuse an ordering required later by ORDER BY or GROUP BY — a merge join on customer_id feeding ORDER BY customer_id saves the final sort.

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, p.amount
FROM orders o
JOIN payments p ON p.customer_id = o.customer_id
WHERE o.placed_at >= '2026-01-01';

Merge Join  (actual time=31840.2..39822.6 rows=12844120 loops=1)
  Merge Cond: (o.customer_id = p.customer_id)
  ->  Sort  (actual time=11204.8..13810.4 rows=18402110 loops=1)
        Sort Key: o.customer_id
        Sort Method: external merge  Disk: 612408kB          -- outer sort spilled
        ->  Seq Scan on orders o  (actual rows=18402110 loops=1)
              Filter: (placed_at >= '2026-01-01'::date)
  ->  Materialize  (actual time=20610.1..24102.8 rows=13040200 loops=1)
        ->  Sort  (actual time=20610.0..22804.2 rows=9120400 loops=1)
              Sort Key: p.customer_id
              Sort Method: external merge  Disk: 402210kB    -- inner sort spilled
              ->  Seq Scan on payments p  (actual rows=9120400 loops=1)
Execution Time: 40118.4 ms
-- Materialize rows (13.0M) > Sort rows (9.1M): duplicate outer keys re-read inner runs
-- both sorts external: roughly 1 GB of temp files before the first joined row

With an index providing order on the inner side:

Merge Join  (actual time=11210.4..21044.8 rows=12844120 loops=1)
  Merge Cond: (o.customer_id = p.customer_id)
  ->  Sort  (actual rows=18402110 loops=1)
        Sort Method: external merge  Disk: 612408kB
  ->  Index Scan using payments_customer_id_idx on payments p  (actual rows=13040200 loops=1)
Execution Time: 21308.2 ms
-- no Materialize: an index scan supports mark and restore itself

Step-by-Step Resolution #

Find the sorts under the merge join and read their Sort Method. quicksort is in memory; external merge with Disk: spilled.
Check whether an index could supply the order. A B-tree on the join key of either input lets the planner replace that Sort with an ordered index scan — worthwhile when the index scan is not dramatically more expensive than a sequential scan plus sort, which depends on index correlation.
```
CREATE INDEX CONCURRENTLY payments_customer_id_idx ON payments (customer_id);
ANALYZE payments;
```
Consider a composite index when a filter and the join key appear together: (placed_at, customer_id) does not give join-key order for a range on placed_at, but (customer_id) INCLUDE (placed_at) or a partial index can.
Compare against a hash join with the merge path disabled, to see what the alternative really costs:
```
BEGIN;
SET LOCAL enable_mergejoin = off;
EXPLAIN (ANALYZE, BUFFERS) <query>;
ROLLBACK;
```
If the sorts must stay, size memory for them. Sorts use plain work_mem; a transaction-level increase for a reporting query avoids the external merge without touching other workloads.
Remove a redundant final sort by aligning ORDER BY with the merge key when the application allows it; the merge join’s output order then satisfies the query.

Before and After #

-- BEFORE: two external sorts and a Materialize
Merge Join → Sort (Disk: 612408kB) + Materialize → Sort (Disk: 402210kB)     Execution Time: 40118.4 ms

-- AFTER: index on payments(customer_id)
Merge Join → Sort (Disk: 612408kB) + Index Scan using payments_customer_id_idx  Execution Time: 21308.2 ms

Why the planner chose sorts over a hash join #

A hash join over the same inputs would have hashed nine million payments. With a small hash memory budget the planner estimated many batches, and each batch re-reads parts of both inputs; two sorts looked cheaper. That estimate depended on the memory settings in force: raising hash_mem_multiplier for the session would likely have flipped the plan to a single-batch hash join, as described in hash_mem_multiplier and the hash memory budget. Neither choice is universally right. Merge joins shine when order comes for free and when the output order is useful downstream; hash joins shine when the smaller input fits in memory. Read the nodes beneath the join before deciding which lever to pull.

The Materialize node deserves a second look in its own right. Its row count exceeding its child’s is not an error: it counts rows returned, including re-reads. When outer keys are highly duplicated — many orders per customer — the re-reads can dominate, and a hash join, which never re-reads, becomes more attractive regardless of sort costs.

Common Pitfalls #

Blaming the Merge Join node. Its own work is a linear walk. Diagnostic signal: large startup time on the join equal to the sorts beneath it. Fix: attack the sorts.

Raising work_mem globally to fix one sort. Every sort in every session benefits and consumes. Diagnostic signal: memory pressure after the change. Fix: scope with SET LOCAL or a role setting.

Indexing a column with poor correlation for order. The ordered index scan may do random heap I/O costlier than the sort it replaces. Diagnostic signal: index scan read buffers near its row count. Fix: include the needed columns to allow an index-only scan, or keep the sort.

Ignoring Materialize spills. A large materialized inner side writes its own temp file. Diagnostic signal: temp I/O attributed to Materialize in BUFFERS. Fix: provide an inner index scan so no materialization is needed.

Frequently Asked Questions #

Why is there a Sort under my Merge Join? #

A merge join needs both inputs ordered by the join key. If no index or lower node supplies that order, the planner adds an explicit Sort, which must read its entire input before the join can start.

What does a Materialize node do in a merge join? #

It buffers the inner input so the join can re-read a run of rows with the same key for each duplicate outer row. Inputs that can rewind themselves, such as index scans and sorts, do not need it.

When does an index remove the sort from a merge join? #

When a B-tree index on the join key is used to scan that input in order. The planner chooses it when the ordered index scan is cheaper than scanning and sorting, which depends heavily on physical correlation and on whether an index-only scan is possible.

Merge Join vs Nested Loop — parent guide: choosing between the algorithms
When Merge Join Beats Hash Join — sibling: the cost conditions that favour merging
Debugging Unexpected Sort Operations — reading Sort Method and spill figures

Up: Merge Join vs Nested Loop