A report query that ran in 90 ms for a year now takes 25 seconds. No code changed, the table grew 4% that week, and a statistics refresh ran overnight. What happened, and what do you do in the next hour as against the next month?

The diagnosis

A cost-based optimiser does not store the plan it used last time. It re-derives a plan from estimated row counts, and those estimates come from the statistics. When an estimate sits near the crossover point between two strategies, a tiny change in statistics flips the choice - and the two choices are not 10% apart, they are orders of magnitude apart.

The classic flip: a nested loop over an index, which is excellent when the outer side returns 50 rows, becomes catastrophic when it actually returns 500,000, because the inner lookup runs 500,000 times. The optimiser chose it because it estimated 50. The performance cliff is not in the data, it is in the estimate.

The usual reason an estimate is wrong by that much is correlation. Optimisers assume predicates are independent, so WHERE city = 'Mumbai' AND country = 'India' is estimated as the product of two selectivities - a tiny fraction - when the real answer is "all the Mumbai rows". A 4% data change is enough to move such an estimate across a boundary.

The misleading clue

The 4% growth. It invites the conclusion that the query is now simply handling more data, which would predict a 4% slowdown, not a 270× one. A latency change disproportionate to the data change is almost always a plan change, and that single heuristic saves hours.

The next hour

1. Capture the plan with actual rows, not just estimates - EXPLAIN (ANALYZE, BUFFERS). The column to read is estimated-versus-actual rows at each node. The node where they diverge by 100× or more is the cause, and everything above it is a consequence.
2. Fix the estimate rather than the plan: raise the statistics target on the offending column, or create extended statistics over the correlated columns so the optimiser stops assuming independence.
3. If the report is business-critical and the clock is running, force the known-good shape as a temporary measure - a plan hint, a disabled join type for that session, or a rewrite that removes the planner's choice (a LATERAL or a materialised intermediate). Record it as a dated workaround with an owner, because a forced plan stops improving when the data changes.

The next month

• Make selectivity legible. A covering index that answers the predicate and the projection removes the planner's degrees of freedom. So does splitting a query that mixes a highly selective filter with a wide aggregation.
• Decide whether you want plan stability as a property. Several engines offer plan baselines or forced plans, and the trade is explicit: you stop getting surprise regressions and you also stop getting improvements, including the ones that matter after a schema change.
• Materialise what does not need to be live. A report query whose answer changes hourly does not need to be computed per request.

The alert that would have caught it

Per-query latency percentiles keyed by a normalised query fingerprint, plus a plan-hash change event. A change in plan hash for a top-20 query is worth a notification on its own, because it is the moment the system's behaviour changed while the code did not.

When this is the wrong answer

If estimated and actual rows agree closely throughout the plan, the optimiser is not the problem and this whole line of investigation is a detour - look at a cold cache, a lock wait, or a resource limit instead. The evidence for a plan problem is the divergence between estimate and reality, not the presence of a slow query.