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

- Asked: 2026-05-17
- Answered: 2026-05-20
- Asked by: Doğa
- Tags: performans, postgresql, veritabani
- Source: https://muhammetsafak.com/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.

## 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](/just-ask/multi-tenant-saas-database-isolation-for-10k-tenants/).

## 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.

## Related Reading

- [Is PostgreSQL Enough for Everything?](https://sade.dev/en/journal/is-postgresql-enough-for-everything/) — sade.dev
- [When should I switch to a covering index with INCLUDE to get an index-only scan?](https://muhammetsafak.com/just-ask/switch-covering-index-include-get-index-only-scan/) — Just Ask
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://muhammetsafak.com/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://muhammetsafak.com/just-ask/pgbouncer-pooling-modes-and-prepared-statements/) — Just Ask