Partial index opportunity

Partial index opportunity
Type: incident simulation · Topic: Query Performance · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-01/10-partial-index-opportunity

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the dashboard query only ever looks at recent *failed* orders — a
small, hot subset — but there was no index, so it did a Seq Scan of the whole
table every time. Indexing all rows (or indexing the low-selectivity status
column) would be wasteful; the query never needs the other 98%.

How it was found: EXPLAIN (ANALYZE) showed a Seq Scan; the filter status='failed'
matches only ~2% of rows — a textbook partial-index opportunity.

The fix: CREATE INDEX idx_orders_failed_recent
ON orders (tenant_id, created_at DESC) WHERE status = 'failed';
The index only stores the failed rows, so it's small and cache-friendly, and the
planner uses it because the query's `status = 'failed'` predicate implies the
index's WHERE clause.

Lesson: when a query always targets a narrow predicate, a partial index beats a
full one — smaller, cheaper to maintain, and just as fast for that path. Don't
reach for a big general index (or a low-selectivity status index) when a partial
index fits the access pattern.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Check how much of the table the query actually touches: it only ever looks at status = 'failed', which is a tiny slice. A full index on all rows would be large and mostly wasted.
2. EXPLAIN (ANALYZE) the query — a Seq Scan to find a small hot subset is the signal for a partial index.
3. Create a partial index: CREATE INDEX ... ON orders (tenant_id, created_at DESC) WHERE status = 'failed'. It's small, hot, and the planner uses it because the query's status filter matches the index predicate.