Low-selectivity index trap

Low-selectivity index trap
Type: incident simulation · Topic: Query Performance · Level: L2 · Duration: 10–15 min
Launch: ride postgres start stage-01/04-low-selectivity-index

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the hot query filters by tenant_id AND status. The tempting fix —
an index on status — is useless, because status has only a handful of values and
'paid' covers a large fraction of the table. The planner correctly ignores a
low-selectivity index (scanning that much of the table via an index is slower
than a Seq Scan). The query stayed slow because nothing indexed the *selective*
column, tenant_id.

How it was found: GROUP BY status showed status is not selective;
EXPLAIN (ANALYZE) showed a Seq Scan and that a status index wouldn't change the
row counts meaningfully.

The fix: CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);
Leading with the selective tenant_id lets the planner seek to a small set of
rows, then status narrows further and created_at gives the ordering.

Lesson: not every index helps. Index the selective predicate, not the column
that's easiest to name. Check cardinality (GROUP BY / n_distinct) before
creating an index, and prefer composite/partial indexes that match the query.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Before adding an index, check how selective your filter is: SELECT status, count(*) FROM orders GROUP BY status. If a value covers a big slice of the table, an index on it alone is nearly useless and the planner will ignore it.
2. Run EXPLAIN (ANALYZE, BUFFERS) on the hot query. The query filters tenant_id AND status — tenant_id is highly selective, status is not. An index that leads with status won't help.
3. Create a composite index that leads with the selective column: (tenant_id, status, created_at DESC). Don't create a standalone index on status.