A report query that ran in under a second for months suddenly takes several minutes, with no change to the query text, the schema, or the indexes. How would you determine whether inaccurate optimizer statistics are the cause, and what would you do about it?

## Why "nothing changed" is never true A plan is chosen from statistics, and statistics track data. If the query text, schema, and indexes are unchanged, then either the data changed, the description of the data changed, or the parameters changed. A sudden regression almost always means the optimizer crossed a cost boundary and switched plan shape — typically from a hash/merge join to a nested loop, or from an index scan to a scan, or the reverse. ## Step 1: get estimated versus actual An estimate-only plan tells you what the optimizer believed, not whether it was right. Every mainstream engine has a mode that executes the statement and reports actual row counts and timings per node. That comparison is the entire diagnostic. Read the plan **bottom-up**. Estimation errors propagate and multiply: if a scan at the bottom is estimated at 1 row when it returns 400,000, then the join above it is estimated at 1 × something, and the join above that inherits the fiction. Only the lowest divergent node is a genuine finding; the rest is fallout. A useful heuristic: an estimate within a factor of 2–3 is fine, a factor of 10 is suspicious, a factor of 1000 is your bug. ## Step 2: classify the estimation error **Stale statistics.** Check the metadata for when the table was last analyzed and how many rows have been inserted, updated, or deleted since. Common causes: - Automatic analysis is threshold-based on a *fraction* of the table. On a table with 500 million rows, a 10% threshold means 50 million changes before it fires — which can be months, or never. - A bulk load, restore, or partition swap wrote a large volume without a following analyze. - A mass delete left the row-count estimate wildly high. - **Growing maximum**: the table is append-only on `created_at`, statistics recorded a maximum from last month, and the query asks for the last hour. Range estimation interpolates inside [min, max], so anything past the recorded max estimates as about one row. This single mechanism causes a large share of "it was fast yesterday" incidents on time-series and event tables. **Fresh statistics but insufficient detail.** - Skew that the histogram resolution cannot represent — too few buckets on a column with a heavy distribution. - Correlated columns estimated as independent, so a multi-predicate filter is underestimated by the product of the individual selectivities. - A predicate wrapped in a function or a type cast, which makes the column statistics inapplicable and drops the engine onto a fixed default guess. **Not a statistics problem at all.** Before committing to the statistics story, rule out: a parameter value that is genuinely atypical (the plan is fine for most values and terrible for this one), data volume that has simply grown past what the old plan could handle, resource contention or a cold cache, or a change in the amount of memory available for sorts and hashes that flipped an in-memory operation to a spilling one. ## Step 3: act 1. **Refresh statistics** on the tables at the bottom of the plan. This is cheap and non-destructive; it samples the table and rewrites the summary. Then re-capture the plan and compare estimates to the same actuals. If the estimates snap into place and the runtime recovers, you have your answer. 2. **If it is still wrong, add detail.** Increase histogram resolution on the specific column driving the misestimate; add multi-column or extended statistics for correlated predicates; gather statistics on the expression if the filter is an expression. 3. **Fix the recurrence, not just the incident.** Add an explicit analyze step at the end of the bulk-load pipeline. Lower the automatic-analysis threshold for the specific huge table so it triggers on an absolute row count rather than an unreachable percentage. For append-only time-series tables, schedule frequent refreshes of the leading timestamp column so the recorded maximum stays close to reality. 4. **Have a containment option.** If the fix cannot land immediately and the query is business-critical, the short-term levers are rewriting the query so the estimate matters less (for example splitting a correlated filter, or materializing an intermediate result), or — where the engine supports it — pinning or hinting the plan. Treat a pinned plan as debt with an expiry date: it freezes a decision made against today's data. ## How to narrate this in an interview The structure interviewers want is: reproduce and measure → get estimated versus actual → find the lowest divergent node → explain *why* the estimate is wrong → fix the estimate rather than the symptom → prevent recurrence. Jumping straight to "I'd add an index" or "I'd hint the plan" without establishing the row-count error is the answer that fails.