Missing index on a hot endpoint

Missing index on a hot endpoint
Type: incident simulation · Topic: Query Performance · Level: L1 · Duration: 10–15 min
Launch: ride postgres start stage-01/01-missing-index

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the hot endpoint filters orders by (user_id, status) and orders by
created_at, but the supporting composite index was missing. With no index the
planner could only do a Seq Scan over the whole table, so a query that returns a
handful of rows had to read millions — latency climbed even though CPU looked
fine (the work was I/O and row filtering, not compute).

How it was found: pg_stat_statements surfaced the query with the highest total
execution time; EXPLAIN (ANALYZE, BUFFERS) confirmed a Seq Scan with a large
"Rows Removed by Filter".

The fix: CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);
This lets Postgres seek directly to the matching rows already in sort order, so
the plan switches to an Index Scan and the LIMIT is satisfied without a sort.

Lesson: latency without a CPU spike is a classic "scanning far more rows than it
returns" signature. Reach for pg_stat_statements + EXPLAIN before touching
connections or killing backends — the cause is usually a plan, not the server.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Latency without a CPU spike often means a query is scanning far more rows than it returns. Find the worst offender in pg_stat_statements (ORDER BY total_exec_time DESC).
2. Run EXPLAIN (ANALYZE, BUFFERS) on that query. A Seq Scan over a big table for a selective filter is the smoking gun.
3. The query filters by user_id and status and orders by created_at. A composite index on (user_id, status, created_at DESC) lets Postgres seek instead of scan. Create it and re-check the plan.