Production engineering
Reveal answer and reasoning
Design performance evidence around a stable logical contract: required filter, projection, ordering and expected result population. Add the index as a deliberate storage choice, observe a real plan and measure a representative owned workload only when the question requires it. Preserve query-result checks even when tuning succeeds, and preserve provider-specific plan notes when upgrading. An efficient wrong query is still wrong.
The portable ordering program is useful independent evidence of the intended sequence, while actual SQL and plan observations establish the external implementation that a production deployment would still need to validate with its own workload.
Before proposing an index, collect the concrete query shapes and write patterns that matter. Compare candidate layouts against those predicates and ordering requirements, then inspect the provider's actual plan. Use parameterized execution for values and keep plans as diagnostic artifacts rather than business return values. If a plan changes after an upgrade, first check result correctness and workload evidence instead of failing an application solely because descriptive wording changed.
A reviewed index decision should include maintenance cost and an evidence boundary, not only a screenshot of a covering-plan label.
What a strong answer covers
- The logical companion and actual query return identifiers2,3,1.
- The fixture index begins with category and supports the stated price/id order.
- A provider-specific covering-plan observation is not a universal performance guarantee.
Common pitfall
Treating an index name or covering label as proof of correctness and universal speed.
Follow-up questions
- What happens to the required order when two prices tie?
- How does adding an unindexed projected column change the investigation?
Self-review
Choose what to revisit. These marks are your own assessment and are separate from lesson completion.