Index Selectivity & Cardinality
AdvancedIndex selectivity — the ratio of distinct values to total rows — determines whether the optimizer will use an index; low-selectivity columns like booleans often trigger full scans instead.
Overview
Selectivity = distinct_values / total_rows. A value of 1.0 (every row is unique, like a primary key) gives maximum selectivity; 0.01 (100 distinct values in 10 000 rows) is moderate; 0.0003 (a boolean in 10 000 rows with 3 values) is very low. The optimizer estimates how many rows a predicate will return using column statistics (maintained by ANALYZE / AUTO ANALYZE). If the estimated row count is high relative to total rows, a sequential scan is cheaper than random-access index hops. Composite indexes boost effective selectivity by combining multiple columns.
When the Optimizer Ignores an Index on a Low-Cardinality Column
An index on a column with only a handful of distinct values (like status) provides little filtering. For a common value like status = 'completed' that matches 70% of rows, a sequential scan touches fewer pages than index hops.
-- orders.status has 4 values: pending, completed, cancelled, refunded
-- Assume 70% rows are 'completed'
CREATE INDEX idx_orders_status ON orders (status);
-- Optimizer likely ignores index for:
EXPLAIN SELECT * FROM orders WHERE status = 'completed';
-- Seq Scan: 70% of rows match → cheaper to scan all pages sequentially
-- Optimizer MAY use index for:
EXPLAIN SELECT * FROM orders WHERE status = 'cancelled';
-- If only 2% of rows are 'cancelled' → index scan is selective enough
-- Check selectivity manually (PostgreSQL):
SELECT
attname AS column_name,
n_distinct,
(SELECT COUNT(*) FROM orders) AS total_rows,
ROUND(ABS(n_distinct)::numeric /
NULLIF((SELECT COUNT(*) FROM orders), 0), 4) AS selectivity
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';Composite Index to Boost Effective Selectivity
Combining a low-selectivity column with a high-selectivity column in a composite index multiplies selectivity. The combined predicate narrows rows far more than either column alone.
-- status alone: low selectivity (4 values)
-- user_id alone: moderate selectivity
-- (user_id, status): high combined selectivity
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Now this query is selective:
EXPLAIN SELECT id, amount, created_at
FROM orders
WHERE user_id = 42 AND status = 'completed';
-- Index Scan: user_id = 42 narrows to ~10 rows, status further filters
-- ANALYZE refreshes statistics after bulk data changes
ANALYZE orders; -- PostgreSQL: manually update statistics
-- MySQL: ANALYZE TABLE orders;
-- Auto-analyze threshold: ~10% of rows changed triggers auto stats updateKey Points to Remember
- 1Selectivity = distinct_values / total_rows; higher is better for index usage (PK selectivity = 1.0).
- 2The optimizer uses column statistics (histogram, n_distinct) to estimate predicate selectivity — stale stats cause bad plans.
- 3Low-selectivity predicates (boolean, small enum) matching a large fraction of rows cause the optimizer to prefer sequential scans.
- 4Composite indexes multiply selectivity — a (user_id, status) index is far more selective than either column alone.
- 5Run ANALYZE (PostgreSQL) or ANALYZE TABLE (MySQL) after bulk loads to refresh statistics and get accurate query plans.
- 6pg_stats.n_distinct in PostgreSQL stores negative values for percentage estimates when the table is large.
Interview Questions
Sign in to ask AriaWhat is index selectivity and why does it affect whether the optimizer uses an index?
You indexed the status column but EXPLAIN shows a sequential scan. Why?
How do column statistics influence the query planner's choice of execution plan?
How does a composite index improve selectivity compared to a single-column index?
Ask Aria about Index Selectivity & Cardinality
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.