An Index the Planner Refuses to Use Is Not a Broken Planner
Key takeaway: An index only helps a query planner when it meaningfully narrows down which rows matter. An index on a column with only a handful of distinct values frequently narrows nothing worth the cost of using it, and the planner’s decision to skip it is usually the right call.
What Selectivity Actually Means
Selectivity describes how much an index narrows the search — an index on a column with a million distinct values across a million rows is highly selective, since matching a specific value identifies essentially one row. An index on a boolean status column with only two possible values across the same million rows is poorly selective, since matching either value still identifies roughly half the table.
A query planner deciding whether to use an available index estimates the cost of using it — following the index, then fetching each matching row’s full data — against the cost of simply scanning the whole table sequentially. For a poorly selective index, following the index to find half the rows and then fetching each one individually is frequently more expensive than just reading the whole table straight through, and the planner correctly chooses the sequential scan instead.
Why This Confuses People Debugging Slow Queries
Someone investigating a slow query notices an index exists on a filtered column, sees the planner is not using it, and concludes the planner is malfunctioning or the index is somehow broken — when in most cases the planner has correctly determined that using this specific index would actually be slower than not using it, given the column’s actual selectivity in the real data.
| Column type | Typical selectivity | Index usually helps |
|---|---|---|
| Unique identifier | Very high | Yes |
| Email address | High | Yes |
| Status enum (3–5 values) | Low | Rarely |
| Boolean flag | Very low | Almost never alone |
| Date, filtered to a narrow range | High for narrow ranges | Yes, if the range is genuinely narrow |
The status enum and boolean cases are the ones that most often prompt a confused investigation, because intuitively “there’s an index and it’s not being used” reads as a bug, and understanding that the planner’s cost-based decision is likely correct given the actual data distribution reframes the investigation productively.
Where a Low-Selectivity Column Still Benefits From Indexing
A composite index combining a low-selectivity column with a high-selectivity one, ordered with the high-selectivity column first, lets the query planner use the index effectively even though the low-selectivity column alone would not have justified one — the combination is often far more selective than either column individually, since filtering on both narrows the result meaningfully even when each alone does not.
A partial index, covering only rows matching a specific condition on the low-selectivity column, can also be genuinely useful — for instance, an index covering only rows where a status column equals a rare value, rather than covering the whole column’s full distribution, is highly selective for that specific value even though the column as a whole is not.
Verifying Rather Than Guessing
Checking the actual query plan the database produces, rather than assuming based on which indexes exist, is the only reliable way to know whether an index is being used and, if not, why the planner made that choice — most database engines provide a way to display the plan along with the planner’s own cost estimates, which directly shows whether it considered the index and rejected it as more expensive, or never considered it as relevant to the query at all.
Verifying the actual data distribution, rather than assuming from the column’s name or apparent purpose, matters too — a status column that looks like it should have five roughly even values might in production actually have one value covering ninety-eight percent of rows, which changes the selectivity calculation substantially from what the schema alone would suggest.
The Bottom Line
Do not assume an unused index is a planner bug — check the actual query plan and the real data distribution before concluding anything is wrong. For genuinely low-selectivity columns, a composite index paired with a more selective column, or a partial index scoped to a specific rare value, is usually more productive than trying to force the planner to use a standalone index that its cost estimate correctly identifies as not worth using.
