A New Way to Think About Borrow Checking (In Reverse)
Concept of the day · databases
Index Selectivity
An index helps only when it eliminates most of the table.
The mechanism. Selectivity is the fraction of rows a predicate keeps. An index lookup costs a tree descent plus, for a non-covering index, one random read per matching row to fetch the rest of the row. A sequential scan reads every page but does so sequentially. Past roughly 5–10% of the table, the random reads lose and the planner switches to a scan. This is why your index on status (three distinct values) is never used, and why you concluded "the planner is broken".
Composite index order. With an index on (a, b), a query on a alone works, a query on b alone does not. The rule is leftmost prefix. Put equality columns first, then the range column, then columns you only need for output. A sort can also be served by the index if its order matches ORDER BY.
Covering indexes. If the index contains every column the query touches (INCLUDE in PostgreSQL, a secondary index with the columns in MySQL), the heap fetch disappears and the random-read cost goes away. That can move the crossover point substantially.
What to actually do. Read EXPLAIN (ANALYZE, BUFFERS). Compare the planner's row estimate with the real count. A 100x misestimate means stale statistics, a correlated predicate the planner assumes is independent, or a function wrapping the column so the index cannot be used at all.
Takeaway. Indexes are not a performance setting you turn on. Each one is a bet about selectivity, and you can read whether the bet paid off.