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.