Optimizer picks nested loop over hash join

WHAT THIS TESTS 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.

A GOOD ANSWER COVERS 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.

COMMON WRONG ANSWERS 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.

LIKELY FOLLOW-UPS 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.

ONE 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.