ORIGINAL TECHNICAL PRACTICE
Explain why an index may not help
A query remains slow after adding an index. What would you investigate before creating more indexes?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
An index helps only when the planner can use it effectively for the query and data distribution. Inspect the execution plan, selectivity, joins and data access volume rather than inferring performance from its existence.
Work through it
- Measure representative queries with realistic parameter values and current statistics. Identify scans, expensive joins, sorts and repeated lookups.
- Check whether predicates match the index’s columns and ordering, and whether transformations or implicit casts prevent an efficient search. Consider a suitable composite or expression index when the database supports it.
- Re-measure read latency, write overhead and storage after a change. Verify that improved performance did not come from changing the query’s result.
Tradeoffs to discuss
- Covering indexes can reduce table lookups but consume more storage and increase write work. A full scan may be preferable when most rows qualify.
Common pitfalls
- Adding many overlapping indexes can harm ingestion. A plan observed on a tiny fixture or one unusual parameter value may not represent production behavior.