What is index selectivity/cardinality, and why does the optimizer care?

Definitions

• Cardinality: the count of distinct values in a column. gender (2-3 values) has low cardinality; email (nearly all unique) has high cardinality.
• Selectivity: distinct_values / total_rows. A selectivity close to 1 means most values are unique — highly selective, so a typical WHERE col = x matches very few rows. A selectivity close to 0 means values repeat heavily — poorly selective, so a typical WHERE col = x matches a large fraction of the table.

Why the optimizer cares

An index lookup for a value that matches, say, 1% of rows is a huge win over a full scan: jump straight to the relevant rows. But an index lookup for a value that matches 50% of rows can be worse than a full scan. Each matched row potentially requires a separate random-access page fetch (via a key lookup, unless it's a covering or clustered index), whereas a sequential full scan reads pages in order, which is much friendlier to disk/OS-level read-ahead and caching.

-- is_active: 95% of rows are TRUE, 5% are FALSE -- very low selectivity for TRUE
CREATE INDEX ix_users_active ON users(is_active);

SELECT * FROM users WHERE is_active = true;
-- Optimizer likely IGNORES the index and does a full table scan --
-- fetching 95% of the table via random-access index lookups would be slower.

SELECT * FROM users WHERE is_active = false;
-- Optimizer likely USES the index here -- only 5% of rows match.

This is why the same index, on the same column, can be used for one query and ignored for another. The optimizer's decision depends on the specific value's estimated selectivity, not just whether an index technically exists.

How the optimizer knows selectivity

Databases maintain statistics: histograms and distinct-value counts per column, refreshed periodically (ANALYZE in PostgreSQL, auto-updated stats in SQL Server/MySQL, or manually triggered). Stale statistics — say, after a bulk load that drastically changes the data distribution — are a common real-world cause of the optimizer picking a bad plan, since it's reasoning from an outdated picture of the data. Running ANALYZE (PostgreSQL/MySQL) or UPDATE STATISTICS (SQL Server) after major data changes is a standard troubleshooting step when a previously-fast query suddenly gets a bad plan.

Low-cardinality columns (booleans, small enums) are usually poor standalone index candidates. An index rarely helps if a typical query still matches a large fraction of the table. They can still be useful as a secondary column in a composite index (e.g., (customer_id, is_active)), where the leading column already narrows things down enough that the low-cardinality column just adds a bit more precision within an already-small result set.