IndexesIntermediate
The index exists but the query does not use it. Why?
“Give the common reasons a planner ignores an index.”
What this tests
- Sargability
- Leftmost prefix
- Selectivity
- Statistics
Answers by level
Read the beginner answer first and notice what is missing.
Four common reasons: the column is wrapped in a function (lower(email)) so the index does not apply; the query skips the index’s leading column (leftmost-prefix rule); the predicate is unselective so the planner correctly prefers a scan; or statistics are stale so the planner misjudges.
Read the plan and the estimates to tell which. Rebuilding an index almost never fixes this.
Green flags · Red flags
Strong green flag · Distinguishes "cannot use" from "chose not to use".
Green flags
- Names sargability and leftmost-prefix
- Knows the planner may be correct to skip
- Reaches for EXPLAIN
Red flags
- Rebuilds the index reflexively
- Wants a query hint (Postgres has none)
Follow-up questions
F1
How do you make WHERE lower(email) = ? use an index?
Scenario
There is a unique index on email but login does a Seq Scan. The query uses lower(email). Explain.