Index selectivity

Index selectivity is the ratio of distinct values to total rows in an indexed column, measuring the index's ability to narrow a query result set. A perfectly selective index — such as a primary key — eliminates all but one row per lookup. A poorly selective index — such as a boolean flag or a gender column — may eliminate only half the rows, leaving the database engine to scan a large fraction of the table anyway. In such cases, the index is not merely useless; it can be actively harmful, because the overhead of random I/O through the index exceeds the cost of a sequential table scan.

Selectivity is a structural property of the data distribution, not of the index itself. A column that is highly selective today may become non-selective tomorrow as the data grows or the distribution shifts. The query optimizer uses cardinality estimation and histogram statistics to decide whether to use an index, but these estimates are always provisional. The index selectivity problem is therefore not a static design choice but a temporal one: the system must continuously re-evaluate whether its indexes still match the data and the workload.