The Low-Selectivity Index Trap
A production incident, replayed by hand: a per-tenant listing is slow even though the filtered column is indexed — and the planner even uses that index. Bring up the same database, prove the index is dead weight, measure selectivity, and replace it with a composite index that actually narrows the search.
A per-tenant order listing filtered by status crawls — and the frustrating part is that somebody already fixed it once: there is an index on status, created after a similar complaint. It changed nothing. This book replays the incident on a database you bring up yourself: schema, data and the inherited index are plain SQL files, nothing is hidden. You reproduce the slowness, catch the query with pg_stat_statements, then confront the paradox — the index is being used on every request, and the query is still slow. The plan shows why: the index condition keeps a fifth of the table, so the real work is fetching a mountain of rows and discarding nearly all of them on the selective filter the index ignores. GROUP BY confirms the cardinality. The fix is a composite index that leads with the genuinely selective column — and dropping the one that only reshuffled I/O. The diagnosis path is the real one you'd use on call.
What you'll build
- Reproduce a slow per-tenant query on a local PostgreSQL stand
- Find the offender with pg_stat_statements
- Read index usage stats and learn why a used index is not a useful one
- Measure filter selectivity with GROUP BY before trusting an index
- Fix it with a composite index that leads with the selective column
Contents
- The stand
- Bring it up
- The schema
- Apply the schema
- The data
- Load it
- Reproduce the pain
- A clean slate, then traffic
- Ask the database what hurts
- The index that didn't help
- Convict it with a plan
- Measure the selectivity
- Design the fix
- Apply it
- Watch the plan flip
- Close the ticket