Production engineering
Reveal answer and reasoning
Choose normalization, collation, NULL handling and row predicates as domain and storage contracts before tuning access paths. Record the exact index definitions and queries used for the observed plan, then repeat provider checks when those definitions change. The portable fixture supplies independent expected selections, but cannot establish SQL expression matching. The real plans supply provider evidence, but cannot establish a universal best index.
A production investigation should preserve both and add representative data and write costs where necessary, so the resulting design explains correctness, maintenance and measured behavior instead of relying on a familiar index name.
For a multilingual catalog, first decide which equality and normalization rules the application promises and which database mechanisms enforce them. Add independent examples beyond ASCII before introducing an expression index as an optimization. For an active-only workload, separately quantify whether the partial population and write maintenance fit the intended query mix. Test the exact provider and parameters, since a syntactically different predicate may not receive the same plan.
Keep any diagnostic plan assertion tied to the owned fixture rather than exposing it as a durable public API contract.
What a strong answer covers
- Known ASCII normalization selects1,2,5; active price order is2,4,1.
- Expression and partial indexes have exact provider matching and row-coverage contracts.
- Observed plan use does not establish cross-engine Unicode semantics or a speed ranking.
Common pitfall
Assuming logically similar text predicates always match the same expression index.
Follow-up questions
- Which Unicode fixtures would challenge the assumed equality rule?
- What query includes rows intentionally absent from the active-only index?
Self-review
Choose what to revisit. These marks are your own assessment and are separate from lesson completion.