Postgres Join Algorithms: Nested Loop vs Hash Join vs Merge Join
Postgres can run the same join three ways - nested loop, hash join or merge join - and the right one depends on table sizes, indexes and sort order. Compare all three on the same query with EXPLAIN ANALYZE and pick the one that fits the data.
The three algorithms
| Algorithm | How it works | Wins when |
|---|---|---|
| Nested loop | For each outer row, look up matches in the inner side | One side is tiny and the other has an index on the join key |
| Hash join | Build a hash table on the smaller side, probe it with the larger | Equality join on unsorted, un-indexed inputs |
| Merge join | Walk two sorted inputs side by side | Both inputs are already sorted on the join key |
Read the plan
EXPLAIN ANALYZE
SELECT c.id, sum(o.amount)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
Look for the join node (Hash Join, Merge Join, Nested Loop), any
Sort nodes feeding it, and the actual time. A merge join that needs two
sorts first is paying for work that a hash join skips.
Forcing a plan to compare
The enable_* planner settings let you switch an algorithm off for one
session, which is a good way to see the alternatives:
SET enable_hashjoin = off;
SET enable_mergejoin = off; -- leaves nested loop
Use this to learn and to diagnose, not in production queries. In production,
fix the inputs instead: add the missing index, update statistics with
ANALYZE, or rewrite the query.
What you do in the project
You run three versions of the same customers-to-orders join, each forced to a different algorithm, compare their plans and times, and choose the one that fits 8,000 un-indexed orders and 100 customers.
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - freeWhat you'll practice
- Reading join nodes and sort steps in EXPLAIN ANALYZE output
- Comparing nested loop, hash join and merge join on the same query
- Matching a join algorithm to table sizes, indexes and sort order
FAQ
What is the difference between a hash join and a nested loop join?
A nested loop looks up matches for each outer row, which is fast when the outer side is small and the inner side has an index. A hash join builds a hash table on one side and probes it, which is usually best for larger equality joins without useful indexes.
When does Postgres use a merge join?
When both inputs are already sorted on the join key, for example from an index scan, or when sorting them is cheap compared with the other options.
How do I see which join Postgres used?
Run EXPLAIN ANALYZE on the query. The join node in the plan names the algorithm, and the output shows rows and actual time for each step.
Keep learning
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - free