After rewriting a slow query, how do you use plans to prove the rewrite actually helped?

## The claim you are making "I rewrote it and it is faster" is three claims at once: the query still answers the same question, it really is faster, and it is faster for a reason that will keep holding. Plans plus measurements let you support all three; each on its own supports none. ## Claim 1: it still returns the same rows This is the one people skip, and it is the one that causes incidents. A rewrite that drops a duplicate, changes NULL handling or reorders a limit is not faster — it is wrong. Verify it mechanically on a representative data set: compare both directions of a set difference and require both to be empty, and compare the row counts, so that duplicate multiplicity is covered too. Run the check for the parameter values that exercise the interesting branches, including one where the result is empty and one where a joined side has no match. ## Claim 2: it really is faster Measure, do not infer: - **Use the measured form of EXPLAIN**, not the estimate-only form. Estimated cost is an internal number on an arbitrary scale; it does not convert to time and is not comparable across two different queries as evidence of speed. - **Same data, same parameters.** Comparing yesterday's slow run on production against today's fast run on a snapshot proves nothing. - **Discard the first run of each.** The first execution pays cold-cache costs; the second query in a session inherits a warm cache and looks better than it is. Alternate the order and take the steadier of several runs. - **Test the worst case, not just the typical one.** Rewrites frequently win on a value that matches ten rows and lose on the value that matches a million. If the query is parameterised, measure both. ## Claim 3: it is faster for the reason you think This is what the plan adds beyond a stopwatch. Before the change, you should be able to point at the offending part of the plan — a predicate applied as a filter with a huge discarded-row count, a scan where you expected an index, an extra sort. After the change, that specific thing should be different: the predicate now positions the index scan, the discarded count has collapsed, the sort node is gone. If the plan looks structurally identical and the query merely ran faster, you measured cache warmth, not an improvement. ## Change one thing at a time A rewrite bundled with a new index and a statistics refresh leaves you unable to attribute the gain. You will then carry all three forever, including the two that did nothing. Apply them separately, re-read the plan after each, and keep only what moved the number. ## Know what you cannot conclude locally Plans depend on data volume, value distribution and the parameters in play, so a laptop result is a hypothesis, not a verdict. Confirm on production-like data, and after shipping, watch the real latency for the endpoint or job that runs the query. The final evidence for a performance change is the production metric, not the plan that predicted it. ## Write it down When the rewrite ships, put the before-and-after plan fragments and the reason in the change description. Six months later, someone will "simplify" the query back to the readable original; the recorded mechanism is what stops that, and it is also what tells you whether the change is still needed after the schema moves on. ## What interviewers listen for The strong answer is a method, not a tool name: prove equivalence, measure fairly with warm runs and realistic parameters, identify the specific plan change that explains the gain, isolate one variable at a time, and confirm in production. Candidates who only say "the cost went down" have never had a rewrite regress on them.