SQL Index Selectivity Calculator

The One Number That Decides If an Index Is Used

Every WHERE clause has a selectivity — the fraction of rows it keeps[wikipedia-database-index]. A filter WHERE status = 'active' on a table where 95% of rows are active has selectivity 0.95 (keeps almost everything); WHERE email = 'a@b.com' on a users table where email is unique has selectivity ~1 / N (keeps one row). The query planner compares that fraction to the cost of reading the index versus scanning the table[postgres-indexes][mysql-indexes]. Above a threshold — often around 5–20% depending on the engine — the planner ignores the index entirely and does a sequential scan, because reading the table directly is cheaper than hopping through the index and then fetching rows one by one[use-the-index-luke].

The math is simple, the consequences are not. A junior adds an index on status (low cardinality, two values) and wonders why EXPLAIN still shows Seq Scan and 100,000 rows read[postgres-explain]. A senior knows the index on status has selectivity 0.5 (if half the rows are active) and the planner will skip it unless the query also filters on a high-cardinality column like created_at or email. A B-tree index on a high-cardinality column like email (cardinality = N) has selectivity 1/N and is almost always used[wikipedia-database-index] — but the same index on gender (cardinality 2–3) is almost never useful alone.

This calculator makes the number explicit. Enter total rows, distinct values (cardinality), and the filtered rows or a WHERE estimate, and it reports selectivity, index efficiency (1 − selectivity), estimated rows returned, and the planner's likely choice (index scan vs sequential scan) with the cost model from EXPLAIN[postgres-explain][mysql-explain]. It shows results in both rows and percent so a team that talks in "10k rows" and a team that talks in "5%" get the same answer without conversion[nist-units]. For the API that sits in front of the database, the API Rate Limit & Cost Calculator models the next bottleneck; for the bundle that ships the query builder, the Bundle Size Impact Calculator models the client.

How to Use This Calculator

The calculator needs three numbers that every EXPLAIN already shows: total rows, cardinality, and filtered rows.

Example 1 — low selectivity, index ignored: status = 'active' on 100,000 rows.

• Total rows: 100,000
• Distinct values: 2 (active/inactive)
• Filtered rows: 95,000 (95% are active) — or leave blank and pick the status example

1. Enter total rows. Type 100000.
2. Enter cardinality. Type 2 for status. The calculator shows average rows per value: 50,000.
3. Enter filtered rows. Type 95000 (or 95% via 95,000). The calculator reports selectivity 0.95 (95%), efficiency 5%, estimated rows 95,000, and verdict: Seq Scan — the index on status alone is not selective enough[use-the-index-luke].
4. Press Calculate. Results: selectivity 95.00%, efficiency 5.00%, cost with index ~95,000 random I/Os vs 100,000 sequential — planner chooses scan.

Example 2 — high selectivity, index used: email = 'a@b.com' on 100,000 users.

• Total rows 100,000, cardinality 100,000 (unique), filtered rows 1 → selectivity 0.001% (0.00001), efficiency 99.999%, verdict: Index Scan — B-tree on email shines[postgres-indexes].

Example 3 — composite: WHERE status='active' AND created_at > '2024-01-01' on 1,000,000 rows.

If 50% are active and 10% are recent, combined selectivity ≈ 0.5 × 0.10 = 0.05 (5%) → 50,000 rows. An index on (status, created_at) has selectivity 5% and will be used; an index on status alone at 50% will not[use-the-index-luke]. Enter 1,000,000, cardinality 50,000 (distinct status+date combos), filtered 50,000 → selectivity 5%, verdict: Index Scan (composite).

Tips while entering:

• Cardinality is distinct values, not filtered rows. For email unique, cardinality = total rows. For status with 2 values, cardinality = 2[wikipedia-database-index].
• If you only know the WHERE clause, estimate filtered rows from EXPLAIN's rows field[postgres-explain] — that is the planner's own estimate.
• The calculator shows both percent and rows so you can sanity-check: 5% of 100,000 is 5,000, not 50.

The Formula

Selectivity is a ratio. All other estimates derive from it.

Let:

• N = total rows in the table
• C = cardinality (distinct values in the indexed column)
• R = rows returned by the filter (WHERE result)

Selectivity (fraction kept):

Average rows per distinct value (if the filter is equality on the indexed column):

For a uniform distribution, R_eq = N / C. Example: N = 100,000, C = 2 → R_eq = 50,000, S = 0.50.

Index efficiency (fraction eliminated):

Planner cost model (simplified sequential vs index, as EXPLAIN reports cost in arbitrary units)[postgres-explain]:

where c_seq is sequential I/O cost (1.0) and c_random is random I/O cost (4.0 by default in PostgreSQL)[postgres-indexes]. If C_idx < C_seq, the planner chooses index scan; otherwise sequential scan[use-the-index-luke].

Reference Table

Selectivity and planner choice for a 100,000-row table, varying distinct values and a filter that keeps one distinct value (R = N / C). The threshold where the planner switches from index to scan is around 10–20% in PostgreSQL/MySQL with default costs[use-the-index-luke] — the table makes the cliff visible.

The cliff is steep: going from 100 distinct (1% selective, clearly indexed) to 10 distinct (10% borderline) multiplies selectivity 10×. Below 100 distinct, a single-column index is questionable; above 1,000 it is almost always worthwhile. For low-cardinality columns, the fix is a composite index that raises cardinality by combining columns[use-the-index-luke].

Practical Tips

1. Check cardinality before creating an index. Query SELECT COUNT(DISTINCT col) FROM tbl — if C < 100 on a 100k-row table, a single-column index on col will be ignored for most values[wikipedia-database-index]. Aim for composite.

2. Use composite indexes low-to-high. Put the most selective column first in (a, b) only if queries filter on a alone; otherwise put the column used in equality first. WHERE status='active' AND created_at > X benefits from (created_at, status) or (status, created_at) depending on which filter is more selective — test both with EXPLAIN[postgres-explain].

3. Read EXPLAIN, not just the index list. EXPLAIN shows rows (planner's estimate = S×N) and cost[mysql-explain]. If rows is 50,000 and cost is 100,000, the planner thinks S=50% and will seq scan — your index is not helping that query.

4. Low selectivity does not mean low value for sorting. An index on status may still help ORDER BY status or GROUP BY status even when WHERE does not use it — the planner uses indexes for ordering too[mysql-indexes].

5. Covering indexes change the math. If the index contains all columns the query needs (a covering index), C_idx drops because no table fetch is needed — random I/O becomes index-only. A SELECT status FROM tbl WHERE status='active' can be index-only even at 50% selectivity[use-the-index-luke].

6. Monitor with pg_stat_user_indexes or INFORMATION_SCHEMA.STATISTICS. Unused indexes waste write overhead and storage. If idx_scan stays 0 for weeks, drop it[postgres-indexes].

7. Re-analyze after bulk changes. After a large INSERT, DELETE, or UPDATE, run ANALYZE so the planner's statistics reflect the new N and C — stale stats are the most common reason EXPLAIN misestimates selectivity and chooses the wrong scan type[postgres-explain].

Limitations

• Uniform distribution assumed. Real data is skewed: status='active' may be 95% selective while status='pending' is 2% on the same column[wikipedia-database-index]. The calculator's S = R/N is correct for the specific WHERE value you enter, but S_eq = 1/C is an average — use EXPLAIN's per-value estimate for skew.
• Single-column model. The calculator models one column's selectivity. Composite selectivity is multiplicative only if columns are independent; correlated columns (e.g., city and zip) violate this[use-the-index-luke].
• Cost constants are defaults. PostgreSQL's seq_page_cost=1.0 and random_page_cost=4.0 are tunable[postgres-indexes]. Lowering random_page_cost to 1.1 for SSDs makes indexes more attractive — the calculator uses defaults.
• No concurrency or caching. Buffer cache hits make random I/O cheaper than the model assumes. A hot index may be faster than the calculation suggests[postgres-explain].
• Rows estimate is planner's view, not reality. EXPLAIN's rows is an estimate from statistics (updated by ANALYZE)[postgres-explain]. Stale statistics misestimate S — run ANALYZE after bulk loads.

Frequently Asked Questions

❓ What is index selectivity?

✅ The fraction of rows a filter keeps: rows returned divided by total rows. 0.001% is highly selective (one row), 50% is not selective (half the table). High selectivity means the index is useful.

❓ When should I create an index?

✅ When the filtered query keeps less than about 10 percent of rows (selectivity under 0.10) on a large table. On a 100k-row table, that is under 10,000 rows. For 50 percent selectivity, a single-column index will be ignored.

❓ Why is my index not being used?

✅ Most often low selectivity — the planner estimates the filter keeps too many rows and a sequential scan is cheaper. Check EXPLAIN's rows and cost. Also check if the column is wrapped in a function (WHERE LOWER(col) = ?) which prevents B-tree use.

❓ What is cardinality?

✅ Distinct values in the column. High cardinality (100k distinct in 100k rows) means unique like email — highly selective. Low cardinality (2 distinct in 100k rows) like status — not selective.

❓ Does a low-cardinality column ever benefit from an index?

✅ Alone, rarely. As part of a composite index that raises cardinality, yes. (status, created_at) where status has 2 values and created_at has 1,000 can be 2,000 distinct combos — selective enough.

❓ What is a covering index?

✅ An index that contains all columns the query needs, so the planner can answer from the index alone without fetching table rows. It can be used even at 50 percent selectivity because random table I/O is avoided.

❓ How do I see the planner's choice?

✅ Run EXPLAIN (PostgreSQL) or EXPLAIN ANALYZE, or MySQL EXPLAIN. Look for Index Scan vs Seq Scan and the rows estimate — that rows number divided by total rows is the selectivity the planner used.

❓ Should I index every column?

✅ No — indexes speed reads but slow writes and use storage. Index the high-selectivity filters in your slow queries, composite where needed, and drop unused indexes (pg_stat_user_indexes idx_scan = 0).