OptimizationAdvanced
Nested loop, hash join, merge join — when does each win?
“Explain the three join algorithms and what makes the planner pick each.”
What this tests
- Physical join algorithms
- How to enable good plans
Answers by level
Read the beginner answer first and notice what is missing.
Nested loop: for each outer row, probe the inner — great when the outer is small or the inner has an index on the join key, quadratic without one. Hash join: build a hash on the smaller input, probe with the larger — O(n+m), needs an equality condition and memory for the build side. Merge join: both inputs sorted on the key, walked together — wins when indexes already provide the order.
You do not pick the algorithm; you enable good ones with an equality condition, an index on the inner join key, and fresh statistics.
Green flags · Red flags
Strong green flag · Explains a slow nested loop as a missing inner index.
Green flags
- Describes all three and their conditions
- Knows how to enable them
- work_mem spill awareness
Red flags
- Does not know the planner chooses
- Cannot say when hash beats nested loop
Follow-up questions
F1
A join has no equality condition. Which algorithm is forced?
Scenario
EXPLAIN shows a Nested Loop with loops=2,000,000 and a Seq Scan inside. What is wrong and what fixes it?