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

> Build one (user_id, status, created_at DESC) index with equality columns first and the sort column last, then drop the single-column indexes it now covers.

- Asked: 2026-05-17
- Answered: 2026-05-20
- Asked by: Doğa
- Tags: performans, postgresql, veritabani
- Source: https://www.muhammetsafak.com.tr/en/just-ask/choosing-the-right-composite-index-order-in-postgresql/
- Language: en-US
- Author: Muhammet Şafak

---
**Question:** I have an `orders` table with millions of rows, and my most frequent query is `WHERE user_id = X AND status = 'completed' ORDER BY created_at DESC`. It's painfully slow right now; the table has separate single-column indexes on `user_id`, `status`, and `created_at`, and when the DB tries to combine them with a Bitmap Index Scan the cost blows up.

What's the most efficient composite index column order? And how does each column's selectivity (cardinality) affect that order?


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, 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 `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.

## Related Reading

- [Is PostgreSQL Enough for Everything?](https://sade.dev/en/journal/is-postgresql-enough-for-everything) — sade.dev
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://www.muhammetsafak.com.tr/en/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask
- [Which PgBouncer pooling mode should I pick — Session, Transaction, or Statement?](https://www.muhammetsafak.com.tr/en/just-ask/pgbouncer-pooling-modes-and-prepared-statements/) — Just Ask
- [Why does a query use an Index Scan in staging but a Seq Scan in production, and how do I diagnose it with EXPLAIN?](https://www.muhammetsafak.com.tr/en/just-ask/why-does-a-query-use-an-index-scan-in-staging-but/) — Just Ask