IndexesBeginner
When should you add an index?
“Walk me through deciding whether to add an index.”
What this tests
- Index tradeoffs
- Selectivity and write cost awareness
Answers by level
Read the beginner answer first and notice what is missing.
Six questions: is the query frequent; does it filter/join/sort on the column; is the predicate selective (under a few percent); is the table large; what is the write rate; and does an existing index already cover it. Any one can be a "no".
Then confirm with evidence: EXPLAIN ANALYZE before, add the index, EXPLAIN ANALYZE after. If the plan did not change, the index was wrong; drop it.
Green flags · Red flags
Strong green flag · Says "indexes are not free" and can enumerate the cost.
Green flags
- Mentions selectivity and write cost
- Reads plans before and after
- Deletes unused indexes
Red flags
- "Index every column"
- Adds indexes without checking the plan
Follow-up questions
F1
Would you index a boolean column?
Scenario
A teammate opens a PR adding indexes to all ten columns of a write-heavy table. What is your review?