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.

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.

1. Equality columns first, sort column last. The rule is clean: columns you filter on with equality (=) go at the front of the index — here user_id and status. The column in your ORDER BY goes last: (user_id, status, created_at DESC). After applying the equality filter, the B-tree already hands rows back in created_at DESC order, so there’s no separate Sort step.
2. Put DESC into the index. If your sort is one-directional (created_at DESC), bake that 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. Cardinality is secondary when both are equalities. Cardinality decides which equality column should lead, but here both 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.
4. 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.
5. Consider a partial index if completed dominates. If most of your queries are status = 'completed', a partial index with WHERE status = 'completed' shrinks the index, keeps 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 user_id/status indexes this one already covers — redundant indexes only slow writes down. 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.