Optimizer-chosen plans can change on their own as data, statistics, or engine versions shift. For a system where some queries must never regress, how do you decide between letting the optimizer keep adapting and pinning plans, and what does that policy cost you?

## The underlying tension A cost-based optimizer re-derives a plan from current statistics on every compile. That is precisely why it is good: as the data grows, skews, or shifts, the plan follows. It is also precisely why it is dangerous: the same SQL that ran one way yesterday can be compiled differently today, and if the new plan is worse the change looks like a spontaneous production incident. Nobody deployed anything; the optimizer simply changed its mind. So you are choosing between two failure modes: - **Adaptive**: usually right, occasionally catastrophically wrong without warning, with no change-management event to point at. - **Pinned**: predictable, and predictably wrong later, since a plan chosen for a 10-million-row table is not necessarily right at 500 million. Neither is a universal answer. The engineering question is how to allocate the risk. ## Segment the workload first Not all queries deserve the same policy. - **Critical path, tight latency budget, high volume** — a login lookup, a payment authorization, an order write path. A plan flip here is an outage. Small in number, high in value; these are pin candidates. - **Bulk and reporting** — long-running, tolerant of a 2× swing, run at low concurrency. Let the optimizer adapt; a regression is a slow night, not an incident. - **Ad hoc and exploratory** — unknowable shapes; pinning is meaningless. Protect the system with resource controls and timeouts instead. The useful rule: pin where **variance** is the problem, not where latency is merely high. If a query is slow but consistently slow, pinning does nothing for you. ## What the mechanisms actually do Different tools have different semantics, and conflating them is a common mistake: - **Hints** embedded in the SQL constrain the optimizer's choices. Some are directives that remove options entirely; others are advisory. They live in application code, so changing them means a deployment — and they silently become wrong as the schema evolves. - **Plan baselines / plan guides / forced plans** live in the database and attach to a statement identity, so they can be added and removed without touching code. Some implementations *force* one specific plan; others accept a set of approved plans and let the optimizer choose among them, which is a materially better property because it bounds the search space rather than freezing a single outcome. - **Query-store style forcing** adds the operationally important piece: automatic detection of a regression, forcing of the previously good plan, and in some engines automatic un-forcing if the forced plan stops being valid or stops helping. - **Optimizer feedback / adaptive execution** goes the other way: instead of freezing plans, the engine learns from observed cardinalities and corrects subsequent compiles, or switches operators mid-flight. Where available this addresses the cause rather than the symptom and is the preferred direction of travel. A property worth asking about every mechanism: **what happens if the pinned plan becomes impossible** — an index dropped, a partition scheme changed. Silent fallback to whatever the optimizer picks means your protection evaporated without a signal. ## Detection beats pinning The highest-leverage investment is not pinning at all — it is making plan change **observable**: - Record the plan identifier alongside query duration percentiles. Then "p99 tripled at 14:20 and the plan hash changed at 14:19" is a five-minute diagnosis instead of a day. - Alert on plan-hash churn for the critical query set specifically. - Keep enough plan history to answer "what did it used to do", so reverting is possible. With that, most teams need very few pins, because they can catch and correct a regression quickly. Without it, teams over-pin defensively — because a regression they cannot see is a regression they cannot fix. ## What pinning costs 1. **Staleness.** A frozen plan is optimal for the data at the moment it was frozen. Data grows and skews; the pin does not. 2. **Upgrade friction.** Engine upgrades change cost models and add operators. Every pin must be revalidated, and a pin that references a plan the new version cannot produce may silently lapse. 3. **Hidden defects.** A pin usually exists because an estimate was wrong. Pinning treats the symptom, so the underlying correlated columns or unestimable predicate stay broken and cause the *next* problem elsewhere. 4. **Debt accumulation.** Pins are added under pressure at 3am and never removed. After two years nobody knows which are still needed or why, and removing one becomes scary. 5. **Foreclosed improvements.** New indexes, better statistics, and new optimizer features cannot help a query whose plan is frozen. Hence the governance rule: every pin carries an **owner, a reason, a review date, and a defined test for removing it**. A pin without an expiry is technical debt with a database attached. ## The policy in one paragraph Default to adaptivity; invest first in detecting plan change and in fixing the estimation defects (statistics maintenance, multi-column statistics on correlated sets, estimable predicates) that cause bad plans in the first place. Pin a small, explicitly enumerated set of critical-path queries where variance itself is the risk, using a mechanism that bounds rather than freezes where available, and manage those pins as reviewed, expiring artifacts. Revalidate them all at engine upgrades. Measure whether the pinned plan is still the best plan periodically, and delete pins that no longer earn their place.