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. Among equality columns the order does not change how much of the index is scanned (the PostgreSQL manual: equality constraints on leading columns limit the scanned portion); user_id goes first because it also serves queries that filter on user_id alone.

3. Redundant indexes aren’t free. The single-column user_id index is now a redundant prefix of the composite, yet it is 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. DESC in the index definition is optional here. PostgreSQL can scan a B-tree backward, so (user_id, status, created_at) also satisfies ORDER BY created_at DESC once the two leading columns are pinned by equality. Spell the direction out when you want the index to document the query, and use it for real when the ORDER BY mixes directions.

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, check the column order: the sort column has to come after the equality columns.

4. Drop the single-column user_id index. The composite already serves every lookup on its leading column. The status and created_at indexes are not prefixes of the composite, so keep them only if other queries use them; check pg_stat_user_indexes before dropping.

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 redundant user_id index. 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.