Parameter · Query Tuning / Planner Method Configuration
enable_mergejoin
Enables the planner's use of merge join plans.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
A merge join reads two inputs that are sorted on the join key and walks them together, like merging two sorted lists. It is excellent when both sides already come sorted (from indexes), and needs no big hash table. This switch turns it off for testing.
For DBAs and operators
Merge Join works for equality joins on sortable types. Each input is either already ordered (an index scan, a previous merge join) or sorted explicitly. It is the only join that can handle a FULL JOIN on a mergeable condition without hashing, and it scales to very large inputs with little memory beyond the sorts.
Measured on 18.6: joining a 200,000-row and a 1,000,000-row table on their indexed id columns (SELECT *): Merge Join of two Index Scans, cost 16,495. With enable_mergejoin = off: a Parallel Hash Join of two Parallel Seq Scans under Gather, cost 35,660.
A merge join can stop early when one input runs out, which the planner takes into account using the column's min/max statistics; stale statistics on an ever-growing key can make this estimate wrong. If an inner side needs to be re-read for duplicate keys, a Materialize node may appear above it. Sort spills (Sort Method: external merge Disk: …) are the usual reason a merge join is slow.
How to change it
Any user can change it for their session with SET.