What is index selectivity and why does it matter?

An index is most useful when it eliminates most rows. If a predicate matches 40 percent of a table, reading the index plus many table pages can cost more than a sequential scan. The planner uses statistics to estimate this before choosing a plan.

SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT status) AS distinct_status,
  COUNT(DISTINCT user_id) AS distinct_users
FROM orders;

How to use the idea

• High selectivity: email, uuid, user_id, or a narrow date range
• Low selectivity: boolean flags, status with few values, or similar enums
• A low-selectivity column can work in a partial index that targets a rare value
• Composite indexes can combine a low-selectivity equality column with a selective one
• Stale statistics produce bad estimates, so run ANALYZE after large data changes
• Histograms and extended statistics help when columns are correlated

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Selectivity is not only about distinct values. The distribution matters: a column can have many distinct values but still be dominated by one value. The planner's histogram captures that skew, which is why fresh statistics are important.