SQL Index Selectivity
How much does this column actually narrow things down? Low selectivity means an index may not help at all.
What & Why
Selectivity describes the fraction of table rows matched by a predicate; smaller matched fractions are generally more selective. Highly selective lookups are stronger index candidates than predicates that return most of a table.
See How It Works
BUSINESS QUESTION
Growth compares the selectivity of each event name in the events table.
| id | user_id | event_name | created_at | properties |
|---|---|---|---|---|
| 1001 | 101 | signup | 2024-01-15 09:12:00+00 | {"source":"organic"} |
| 1002 | 101 | activated | 2024-01-16 14:25:00+00 | {"step":"workspace"} |
| 1003 | 102 | signup | 2024-02-10 11:08:00+00 | {"source":"paid"} |
| 1004 | 103 | report_viewed | 2024-03-12 16:40:00+00 | {"report":"retention"} |
EXAMPLE QUERY
SELECT
event_name,
COUNT(*) AS matching_rows,
ROUND(COUNT(*)::numeric / NULLIF(SUM(COUNT(*)) OVER (), 0), 4) AS table_fraction
FROM growth.events
GROUP BY event_name
ORDER BY table_fraction, event_name;RESULT — event-name selectivity
| event_name | matching_rows | table_fraction |
|---|---|---|
| activated | 1 | 0.2500 |
| report_viewed | 1 | 0.2500 |
| signup | 2 | 0.5000 |
The ordered fractions come from all four representative growth.events rows.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario