Wrong column order in a composite index

Wrong column order in a composite index
Type: incident simulation · Topic: Query Performance · Level: L2 · Duration: 10–15 min
Launch: ride postgres start stage-01/02-wrong-composite-index-order

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: an index existed, so it was tempting to conclude "indexing is fine".
But a B-tree composite index is only useful following its leftmost columns. The
index was (status, created_at, tenant_id); the hot query filters by tenant_id
and status and sorts by created_at. Leading with the low-selectivity `status`
column meant Postgres couldn't use the index to seek on tenant_id — it fell back
to a Seq Scan + Sort, or scanned far too much of the index.

How it was found: pg_stat_statements surfaced the slow query; \d orders showed
an index was present; EXPLAIN (ANALYZE) showed tenant_id sitting in a Filter (or
a Seq Scan + Sort) rather than an Index Cond.

The fix: CREATE INDEX idx_orders_good ON orders (tenant_id, status, created_at DESC);
Now the leading columns match the equality predicates and the trailing column
matches the ORDER BY, so the plan becomes an Index Scan that returns rows already
sorted — no Sort, no full scan.

Lesson: "an index exists" is not "the right index exists". Order composite index
columns by how the query uses them: equality predicates first (most selective
first), then the ORDER BY column. Don't churn through random indexes — read the
plan and design one.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. There IS an index on the table — check it with \d orders. The question isn't 'is there an index' but 'is it usable for THIS query'.
2. Run EXPLAIN (ANALYZE, BUFFERS) on the hot query. If the index only appears as a Filter (or isn't used at all) and there's a Seq Scan / big Sort, the column order is wrong.
3. A B-tree composite index is only useful left-to-right. The query filters tenant_id + status and sorts by created_at, but the index leads with status. Create (tenant_id, status, created_at DESC) so Postgres can seek and return rows already ordered.