Hash Join vs Merge Join vs Nested Loop
Recognise the three main join strategies, their cost profiles, and when each is the planner's best choice.
Three Join Strategies
PostgreSQL has three physical join algorithms:
- Nested Loop — for each outer row, scan inner
- Hash Join — build hash of inner, probe with outer
- Merge Join — both sides sorted, merge in lockstep
Nested Loop
Simplest: outer × inner. Fast when inner has a good index AND outer is small:
EXPLAIN ANALYZE
SELECT * FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.id = 42;
-- Nested Loop
-- -> Index Scan on users where id = 42 (rows=1)
-- -> Index Scan on orders_user_id_idx (rows=5)All lessons in this course
- Reading EXPLAIN and EXPLAIN ANALYZE
- Sequential Scans vs Index Scans
- Hash Join vs Merge Join vs Nested Loop
- Identifying and Fixing Slow Queries