Optimizer picks nested loop over hash join
optimizer reasoning.
nested loop wins on few rows, so bad row estimates from stale stats or skewed data trick it; fix by refreshing statistics, adding histograms, rewriting predicates, or ensuring memory for hashing.
What's really being asked
The interviewer wants senior-level reasoning about why a cost-based optimizer goes wrong and a disciplined fix sequence, not a reflex to force hints.
The full answer
A nested loop join scans the outer relation and probes the inner for each row, so it is cheap only when the outer side yields few rows and the inner has a supporting index. A hash join builds a hash table on the smaller input then streams the larger, winning when both inputs are large. The optimizer chooses nested loop because it estimates the outer side returns few rows. That estimate goes wrong when statistics are stale after bulk loads, when histograms are missing so it assumes uniform distribution over skewed data, when predicates are correlated and the optimizer multiplies selectivities as if independent, or when out-of-date row counts mislead cardinality math. To influence it: run ANALYZE to refresh statistics, raise the statistics target or add extended statistics for correlated columns, rewrite predicates so they are sargable and estimable, ensure work_mem is large enough that a hash join is not penalized for spilling, and verify with EXPLAIN ANALYZE that estimates now match actuals. Only after that, if the model is genuinely deficient, consider a join hint.
The mistakes people make
Forcing a hash hint immediately without diagnosing the bad estimate, which masks the real problem and breaks when data shifts. Blaming the optimizer rather than the statistics. Ignoring correlated columns and memory limits.
What usually comes next
How do correlated predicates fool selectivity math; what is extended statistics; how does work_mem affect hash joins; how do you detect a cardinality misestimate.
A concrete example
After a nightly bulk load, a join estimates 12 outer rows but returns 4 million, so a nested loop probes the inner table millions of times. EXPLAIN ANALYZE exposes the 12-versus-4-million gap. Running ANALYZE refreshes the row count, the optimizer now expects millions, and it switches to a hash join, cutting runtime from minutes to seconds.
Interview question
The optimizer chose a nested loop because it estimated 10 outer rows, but 5 million were returned. What is the most appropriate first action?
- a.Immediately add a hint forcing a hash join
- b.Drop all indexes so the optimizer must scan
- c.Refresh table statistics so cardinality estimates reflect realityCorrect
- d.Increase the isolation level to serializable
Why? this is the answer
The root cause is a cardinality misestimate, usually from stale statistics, so refreshing them lets the optimizer choose correctly on its own. Hints mask the problem, dropping indexes worsens it, and isolation level is unrelated to join choice.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #databases
- #query-optimization
- #join-algorithms
- #statistics
- #cardinality-estimation
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles