Index Selectivity

Selectivity measures how many rows an index condition filters out. High-cardinality predicates and well-ordered composite indexes make lookups efficient; low-selectivity indexes often lose to scans.

data-integrity

Definition

Index selectivity is the proportion of rows a predicate excludes, often estimated as distinct values divided by total rows for a column. A highly selective index returns a small fraction of the table and is usually efficient. A low-selectivity index, such as a boolean flag on a large table, may return most rows and can be slower than a sequential scan once index and heap access are counted.

Context and scope

Selectivity guides index design and query tuning. Composite indexes are ordered: a leading column with high selectivity narrows the search, while later columns refine it. The query planner uses statistics to choose an index; stale statistics can make a good index look bad.

Common misconceptions

More indexes always improve performance — every index adds write cost and storage
A single-column index on every column is optimal — composite indexes often serve multi-predicate queries better
An index is always used when present — the planner may choose a sequential scan for low selectivity or small tables

Anti-patterns

Indexing a boolean column without a partial index or composite leading column
Putting a low-selectivity column first in a composite index used for selective queries
Creating overlapping indexes that duplicate the same leading columns

Related concepts

Related examples

Authoritative source

https://www.postgresql.org/docs/current/indexes-intro.html

What makes an index useful

An index on email is selective because most values are unique. An index on active (true/false) is not: a query for active users may read most of the table, and the planner may prefer a sequential scan.

-- High selectivity: narrows to one or a few rows
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);

-- Low selectivity alone; useful as a partial index
CREATE INDEX orders_pending_idx
ON orders (created_at)
WHERE status = 'pending';

Composite order matters

For WHERE tenant_id = $1 AND status = $2 ORDER BY created_at DESC, an index on (tenant_id, status, created_at) can serve filtering and ordering. An index on (status, tenant_id, created_at) may not narrow as effectively when status is low-selectivity.

Measure, do not guess

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20;

Look for an index scan with a small number of buffers and rows. If the plan chooses a sequential scan, check statistics with ANALYZE and compare actual row counts with estimates.

When to add an index

Add an index when a query is frequent, selective, and latency-sensitive. Compared to denormalization, an index preserves the data model but adds write overhead. Compared to caching, it improves the source query rather than duplicating results.

Pitfalls

Indexes consume storage and slow inserts, updates, and deletes. Unused indexes are pure overhead; review usage statistics. Functions on indexed columns can prevent index use unless a matching expression index exists. Test with production-like data distributions; small test tables rarely exercise planner choices.