Given a slow query and its execution-time plan, how do you decide whether the right fix is a new index, a query rewrite, better statistics, or accepting the query as it is?

## Classification before treatment The common failure mode is reflexive: the plan shows a full scan, so add an index. A principled route classifies the fault first, using evidence already in the execution-time plan. **Ratio 1 - rows returned versus rows touched.** Sum what the leaves actually read and compare to what the root returned. Returning 200 rows after touching 40 million is a selectivity problem: the engine is reading data it immediately discards. That is the classic case for an index on the filtering columns, ideally one that also covers the projected columns so no row lookups are needed. **Ratio 2 - estimated versus actual rows.** Walk bottom-up to the lowest node where the two diverge by an order of magnitude or more. If that gap exists, the plan you are looking at was chosen for a different problem than the one being executed. Fix estimation first: refresh statistics, add multi-column statistics for correlated predicates, or rewrite a predicate that hides a column inside an expression. Physical design decisions taken on top of a broken estimate tend to be wrong and then get frozen into the schema. **Ratio 3 - exclusive time.** Subtract child time from parent time and rank operators. Time concentrated in a sort or hash points at memory and ordering; time spread thinly across a nested loop with a huge loop count points at repetition; time in a leaf scan points at access path. ## The four verdicts 1. **Index.** Right when estimates are accurate, selectivity is high, and the query is frequent enough to matter. Design it deliberately: leading columns for equality predicates, then a range column, then columns needed only for output if you want covering behaviour. Before creating it, check whether an existing index has the right leading columns - the cheapest new index is often the one you already have, used properly after a predicate rewrite. Price the cost: every index adds write amplification on insert, update and delete, consumes storage and cache, and lengthens maintenance operations. Automated missing-index suggestions are single-query, greedy and blind to the rest of the workload; treat them as an input, never as a decision. 2. **Statistics.** Right when the estimate-versus-actual gap is the dominant defect. It is the cheapest fix, has no ongoing write cost, and often fixes a family of queries at once rather than one. 3. **Rewrite.** Right when the plan reveals work the query never needed: a DISTINCT compensating for a fan-out join, a sort inside a subquery whose order is discarded, an OR that prevents index use and can become a UNION ALL, a correlated subquery evaluated per row, a projection dragging large columns through a sort, or an application issuing one query per row where one set-based query would do. Rewrites are usually the highest-leverage change because they reduce the work rather than accelerating it. 4. **Accept or re-architect.** Right when the plan is honest: the query really must read a very large fraction of a very large table. No index converts a full aggregation into a lookup. The options move up a level - precomputed summaries or materialized results refreshed on a schedule, partitioning so that only relevant partitions are read, running the workload against a replica so it does not compete with transactional traffic, or changing the product requirement (a smaller time window, an approximate count, an asynchronous report). ## Weighing before acting Three questions keep the decision honest: - **Does this query matter?** Total load is frequency times duration. A 4-second query run twice a day matters far less than a 40 ms query run 2,000 times a minute. Optimizing the visible-but-rare query is a common waste. - **What does the fix cost the rest of the system?** Indexes tax writes; increased memory budgets tax concurrency; materialized results add staleness and maintenance; forcing plan shapes adds fragility as data changes. - **How will I know it worked?** Set the target before changing anything, in measured terms - actual rows touched, exclusive time, and end-to-end latency under realistic concurrency. Do not accept a lower estimated cost as proof, because cost is computed from the same estimates that may be the defect. ## Sequencing When several faults coexist, order matters: correct the estimate, then remove unnecessary work by rewriting, then add physical structures, then tune resource budgets, and only then consider architectural changes. Working in that order avoids permanent schema debt created to compensate for a temporary statistics problem.