Three Physical Strategies, One Logical Join
A SQL JOIN clause is a logical operation, but PostgreSQL must translate it into one of three physical algorithms: nested loop, hash join, or merge join. Each has a different cost profile depending on table sizes, whether an index exists on the join column, whether the input is already sorted, and how much of it fits in work_mem, and the planner chooses among them the same way it chooses between a seq scan and an index scan, purely by estimated cost.
Cricket analogy: A captain deciding how to dismiss a set batsman can choose an attacking slip cordon, a defensive field, or a spin trap depending on conditions, just as the planner chooses nested loop, hash, or merge join depending on table sizes and available indexes.
Nested Loop Join
A nested loop scans the outer relation row by row and, for each outer row, probes the inner relation, ideally via an index lookup rather than a full scan, to find matching rows. It is the only join strategy that works with any join condition, including inequality and range conditions, and it wins decisively when the outer side is small and the inner side has a good index, because total cost scales roughly as outer_rows times inner_probe_cost rather than requiring the whole inner table to be read or hashed.
Cricket analogy: A fielding coach checking each of the opposition's 11 batsmen one by one against a scouting index of known weaknesses is a nested loop: for each outer batsman, a quick lookup finds the matching dismissal plan, cheap when the index of weaknesses is well organized.
Hash Join and Merge Join
A hash join builds an in-memory hash table on the smaller input keyed by the join column, then streams the larger input probing the hash table for matches, and it dominates for large, unsorted equi-joins as long as the hash table fits within work_mem, spilling to disk in batches otherwise. A merge join instead requires both inputs sorted (or already produced in sorted order, e.g. from an index scan) on the join key, then walks both sorted streams in lockstep like a zipper; it excels when both inputs are already sorted for another reason, such as an ORDER BY that matches the join key, avoiding a separate sort step.
Cricket analogy: Building a quick lookup table of every bowler's economy rate in memory before matching them against a list of upcoming batsmen is a hash join; instead, walking two already-sorted batting-order lists in lockstep to match partnerships chronologically is a merge join.
-- Force comparison: disable hash/merge to see the planner fall back to nested loop (diagnostic only)
SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN ANALYZE
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_id = 583920;
RESET enable_hashjoin;
RESET enable_mergejoin;
-- A typical large equi-join that PostgreSQL will plan as a hash join
EXPLAIN ANALYZE
SELECT o.order_id, p.product_name, o.quantity
FROM order_items o
JOIN products p ON p.product_id = o.product_id;
-- -> Hash Join (cost=310.00..98213.55 rows=2100000 width=48) (actual time=4.011..812.334 rows=2103981 loops=1)
-- Hash Cond: (o.product_id = p.product_id)
-- -> Seq Scan on order_items o ...
-- -> Hash (cost=210.00..210.00 rows=8000 width=24) ...
-- -> Seq Scan on products p ...enable_seqscan, enable_hashjoin, enable_mergejoin, and enable_nestloop are session-level switches meant purely for diagnosis, letting you see what plan the planner would choose if a preferred strategy were unavailable. Never leave them disabled in a production session's default configuration.
Choosing the Right Strategy in Practice
If you see a nested loop with a high loops count and no matching index on the inner relation's join column, the fix is usually to add that index, since the planner will happily switch to nested loop with index probes once one exists and the outer side is reasonably small. If instead you see a hash join whose Hash node reports 'Batches: 4' or higher in the EXPLAIN ANALYZE output, that means the hash table spilled to disk because it exceeded work_mem, and increasing work_mem for that session or query can convert it back to a single in-memory batch, often producing a large speedup.
Cricket analogy: Noticing a fielding captain repeatedly sending a runner all the way to the boundary to fetch the ball because there's no relay fielder positioned there is like a nested loop lacking an index; adding a relay fielder (an index) turns each fetch into a quick, cheap lookup.
Raising work_mem globally to fix one query's disk-spilling hash join is risky: work_mem is allocated per sort/hash operation per connection, so a high global value can multiply across many concurrent connections and complex queries, exhausting server memory. Prefer SET work_mem = '256MB' scoped to the single session or query, not a global postgresql.conf change, unless you have sized it against max_connections.
- PostgreSQL implements every logical JOIN as one of three physical algorithms: nested loop, hash join, or merge join.
- Nested loop is the only strategy supporting arbitrary join conditions and wins when the outer side is small with an indexed inner side.
- Hash join builds an in-memory hash table on the smaller input and dominates large unsorted equi-joins that fit work_mem.
- Merge join requires both sides sorted on the join key and wins when that sort is already available, e.g. from an index or ORDER BY.
- A high loops count on a nested loop without an inner index usually means a missing index is the fix.
- Hash node 'Batches > 1' in EXPLAIN ANALYZE indicates the hash table spilled to disk past work_mem; raising work_mem can fix it.
- enable_* planner switches are diagnostic tools only, never a production default configuration change.
Practice what you learned
1. Which join algorithm is the only one that supports arbitrary join conditions, including inequalities and ranges?
2. What does 'Batches: 4' in a Hash node's EXPLAIN ANALYZE output indicate?
3. When does a merge join tend to be the cheapest choice?
4. You see a nested loop join with loops=50000 and the inner side is a Seq Scan. What is the most likely fix?
5. Why should work_mem generally be raised per-session or per-query rather than globally in postgresql.conf?
Was this page helpful?
You May Also Like
Reading EXPLAIN ANALYZE
Learn to interpret PostgreSQL's EXPLAIN ANALYZE output so you can tell what a query planned versus what it actually did, and use that gap to fix slow queries.
The Query Planner and Cost Estimation
Understand how PostgreSQL's planner generates candidate execution plans and estimates their cost so it can pick the cheapest one.
Common Table Expressions
Learn how PostgreSQL's WITH clause structures complex queries into named, readable building blocks, including recursive CTEs for hierarchical data.