How do I choose the right composite index column order in PostgreSQL?

Short answer: build one composite index for this query — (user_id, status, created_at DESC). Equality columns first, the ORDER BY column last; everything else falls out of that.

Short answer

The slowness isn’t a missing index, it’s the wrong index shape: with three separate single-column indexes the DB has to merge them via a Bitmap Index Scan and then run a separate Sort step — both expensive. I covered the tenant-scoped version of the same “which column should the hot table be organised by” question in the multi-tenant isolation answer.

Why

1. Equality columns belong in front, the sort column last. After applying the equality filter the B-tree already hands rows back in order, so no separate Sort step is needed. Break that order and the planner has to sort by itself.

2. Cardinality is secondary here. Both columns are equality predicates, so the real win is moving the sort into the index; still, putting the higher-selectivity column (usually user_id) first narrows the initial scan.

3. Redundant indexes aren’t free. The single-column indexes this composite covers no longer help reads but are still updated on every write.

What to do

1. Build the (user_id, status, created_at DESC) composite. Equality columns first, the ORDER BY column last.

2. Put DESC into the index definition. On a single column PostgreSQL can scan backwards, but pinning the direction in a composite guarantees the planner’s job and makes the sort free.

3. Confirm with EXPLAIN (ANALYZE, BUFFERS). The plan you want is a single Index Scan — not Bitmap Index Scan + Sort. If you still see a Sort, your column order or DESC direction is wrong.

4. Drop the covered single-column indexes. The composite already satisfies the user_id and (user_id, status) prefixes; the separate ones only slow writes down.

5. Consider a partial index if completed dominates. A partial index with WHERE status = 'completed' shrinks the index, may keep more of it in RAM, and lowers write cost.

Bottom line: I’d build the (user_id, status, created_at DESC) composite index, confirm with EXPLAIN (ANALYZE, BUFFERS) that it drops to an Index Scan, and then drop the single-column indexes it already covers. If your traffic piles onto one status, tighten it further with a partial index. For a deeper take on where indexing meets native SQL, see the sade.dev piece.