Plan Instability

A report that ran in 90 ms for a year takes 25 seconds this morning. No deploy, no schema change, 4% more data, and a statistics refresh overnight. The query did not change and its plan did.

A cost-based optimiser does not remember the plan it used last time. It estimates how many rows each step will produce and picks the cheapest combination of access methods and join strategies for those estimates. The estimates come from sampled statistics. When an estimate sits near the crossover point between two strategies, a small shift flips the decision - and the two sides of that crossover are not 10% apart.

The canonical flip: a nested loop driven by an index is excellent when the outer side yields 50 rows and catastrophic when it yields 500,000, because the inner lookup runs once per outer row. The optimiser chose it for 50. The cliff is in the estimate, not in the data.

Why it matters

It produces the hardest class of production incident to triage: a severe, sudden latency regression with no change in any artefact anyone controls. Teams look for a deploy, find none, and start restarting things.

It also breaks a reasonable intuition - that performance is a property of a query. Performance is a property of a query plus a statistics snapshot, and the second half changes on a schedule you may not know. Any capacity plan or latency SLO that assumes otherwise is one analyse job away from being wrong.

Implementation patterns

• Read estimates against actuals, not costs. EXPLAIN (ANALYZE, BUFFERS) and its equivalents show both; the node where they diverge by 100× or more is the cause, and every node above it is a consequence. Cost numbers are the optimiser's opinion and are not evidence.
• Fix the estimate before the plan. Raise the statistics target on the offending column, or create extended statistics over correlated columns so the optimiser stops multiplying independent selectivities for predicates that are not independent.
• Remove the choice where the stakes are high. A covering index that satisfies predicate and projection, or a rewrite that materialises an intermediate result, leaves the optimiser fewer ways to be wrong.
• Plan pinning as a dated workaround, with an owner and a review date - plan baselines, forced plans, or hints where the engine supports them.
• Alert on plan-hash change for the top queries by volume, not only on latency. It is the event that corresponds to the cause.

Industry example

The mechanism is documented by every major optimiser's own manual. Postgres's documentation is explicit that the planner assumes independence between predicates and offers extended statistics specifically to correct it; Oracle shipped plan baselines and SQL plan management as a product feature because customers needed plan stability as a guarantee; SQL Server's parameter sniffing behaviour - a plan compiled for one parameter value and reused for a very different one - is the same phenomenon arriving through plan caching rather than through statistics.

That three independent vendors built explicit plan-stability features is the strongest evidence that this is a structural property of cost-based optimisation, not a defect in any one engine.

Failure scenarios

• The overnight flip, where an analyse job moves an estimate across a crossover and the morning's reports time out.
• Parameter sniffing, where the first parameter value compiles a plan that is wrong for the rest - a report fast for one customer and unusable for the largest.
• Correlated predicates estimated as independent, producing errors of several orders of magnitude on perfectly ordinary queries.
• A new index making things worse, because it offers the optimiser a tempting access path for an estimate that is wrong.
• A pinned plan outliving its justification, so the query stays on a plan chosen for a data distribution that no longer exists.

Trade-offs

When not to use it

Plan pinning is the part to be careful with, and the rule is narrow: pin during an incident or for a critical, stable-shaped query, and never as a general policy. A pinned estate stops benefiting from the optimiser entirely, and the pins accumulate as undocumented constraints that nobody dares remove.

Equally, do not reach for any of this when the evidence is absent. If estimates and actuals agree throughout the plan, the optimiser is not your problem - look at a cold cache, a lock wait, a saturated disk or a changed data volume. The signature of plan instability is the divergence between estimate and reality, not the mere presence of a slow query.

Interview question

Q: Overnight, one of your top ten queries went from 90 ms to 25 seconds with no deploy. Tell me what you check, in order, and what you would put in place afterwards so that the next occurrence is a notification rather than an incident.

What a strong answer covers: the disproportion between a 4% data change and a 270× latency change as the signal that this is a plan change; capturing estimates against actuals and finding the first large divergence; correcting the estimate with statistics targets or extended statistics rather than reaching for a hint; a time-boxed pin or rewrite if the clock is running, recorded as a dated workaround; and plan-hash change alerting on high-volume queries as the structural improvement.

Quick check

Quiz: Why can a 4% increase in data cause a 270× increase in query latency? Because the optimiser re-derives the plan from estimated row counts, and a small statistics shift can move an estimate across the crossover between two strategies whose costs differ by orders of magnitude.

Flashcard: Which number in a query plan is evidence and which is opinion? Actual rows against estimated rows is evidence; the optimiser's cost figures are its opinion and explain only why it chose what it chose.