The decision that matters
Capture the query shape, parameters and frequency. Use EXPLAIN to understand the planned access path, then use execution analysis carefully in an appropriate environment because it runs the statement. Compare estimated and actual row counts where available. Look for large scans, expensive sorts and filters that remove most rows after work has already been done. Keep representative data distribution in mind.
A practical scenario
A task list filtered by organization and status, ordered by update time, may need a different index from a global search across titles. Testing with ten rows can hide the difference. Build a representative dataset and compare the plan and latency before and after a candidate index. Also inspect the effect on the writes that maintain it.
Before you ship
- Measure the query under representative filters and data volumes.
- Check existing indexes before adding another.
- Compare read improvement with write and storage costs.
A useful next step
Keep the before-and-after plan with the change description. If the improvement depends on a particular data distribution, document that assumption. Query performance work is easier to review when it includes evidence rather than a general claim that indexing makes everything faster.
Primary reference & further reading
This guide combines general engineering recommendations with an illustrative scenario. Provider behavior can change; check current documentation before applying it. Read our editorial approach.