A report query that returned in milliseconds during testing now runs for hours in production on the same plan shape — an index-driven nested loop join. What property of that operator makes it so sensitive, and how would you reason about the regression?

## Why this operator is the fragile one Every join algorithm has a cost model, but their sensitivities differ. A hash join reads each input once; if the estimate is off, cost is wrong by a constant factor and possibly some spilling. A merge join over sorted inputs is likewise a linear pass. An index nested loop's cost is n_outer multiplied by a per-probe constant, and that constant is not small: an index descent plus, usually, a random base-table fetch per matching row. Being wrong about n_outer scales the whole join, and unlike the other operators there is no point at which the strategy switches to something more suitable — execution follows the plan that was chosen. ## The crossover it sits near There are two regimes. Few outer rows: probing is dramatically cheaper than reading the inner table, and the loop wins by orders of magnitude. Many outer rows: the probes collectively touch much of the inner table anyway, in random order, and one sequential scan plus a hash build would have been far cheaper. The cardinality estimate places the query on one side of that crossover. Small errors near the crossover cost a little; large errors cost hours. ## What actually differs between test and production Volume is the obvious one: a test dataset of a few thousand rows keeps everything in cache, so even a bad plan looks instant. Distribution matters more — predicates that are selective on synthetic data may match a large fraction of real rows, and skew means the average case in statistics is not the case being run. Correlated predicates are a classic trap: optimizers typically multiply selectivities as if conditions were independent, so two correlated filters produce an estimate far below reality. Parameter values matter when a plan chosen for one value is reused for another with very different selectivity. Statistics may be stale after bulk loading. And the inner index may be unusable in production because of a type mismatch, an expression wrapped around the column, or an index that was never created there. ## How to diagnose Get an execution plan with actual row counts, not just estimates. Compare estimated with actual on the outer branch first — that is the assumption under test. Then look at how many times the inner branch executed and the rows returned per execution; multiplied together they give the real work done. If actuals dwarf estimates on the outer side, the diagnosis is a cardinality problem, not an indexing problem. If executions are as expected but each is slow, the inner access path or cache behaviour is at fault instead. Comparing buffer or read counts between environments separates cache effects from genuine algorithmic cost. ## Fixes, roughly in order Refresh statistics and, where supported, add multi-column or expression statistics so correlated predicates estimate better. Reduce the outer side: a more selective predicate, a filter applied earlier, or a rewrite that makes the small side genuinely small. Make the inner probe cheap with a covering index so the random base-table fetch disappears. If the join truly is large-to-large, the fix is a different operator — make hash or merge join viable and, if necessary, steer the optimizer there. Plan stability mechanisms are a last resort: they freeze today's choice, which is only right if you are confident the data shape will not move. ## The prevention framing The deeper lesson is that plans chosen on the assumption of a small outer input are a latent risk on any table that grows. Testing on production-shaped data volumes and distributions, not just production schema, is what surfaces this before users do. Monitoring queries by total work rather than average latency catches the skewed parameter values that only occasionally take the expensive path.