Postgres composite indexes: seven patterns teams get wrong
A Postgres composite index with columns in the wrong order is decoration. The planner uses leftmost-prefix rules — equality columns first, range columns last — and seven common patterns turn indexes into write overhead without read benefit.
The index exists. EXPLAIN shows an Index Scan. p99 latency is still 800 ms. The composite index on (created_at, tenant_id, status) was created because those three columns appear in the WHERE clause — but the query filters tenant_id = 42 and status = 'active' with created_at > now() - interval '30 days'. Postgres uses the index for the date range first, reads thirty days of rows across all tenants, then filters. The Postgres composite index is working exactly as btree ordering dictates. The team built decoration.
Composite indexes are sorted lexicographically: rows ordered by column one, then within equal values of column one by column two, and so on. The planner can only use the index efficiently when the query provides a contiguous prefix of columns — starting from the left. Understanding that rule prevents seven recurring misuse patterns that waste disk, slow writes, and fool dashboards that count indexes without reading plans.
The leftmost-prefix rule governs every composite index decision
A btree index on (a, b, c) supports:
WHERE a = ?WHERE a = ? AND b = ?WHERE a = ? AND b > ?WHERE a = ? ORDER BY b
It does not support:
WHERE b = ?aloneWHERE a = ? AND c = ?withoutbin the predicate (Postgres may use skip scan in some versions, but do not rely on it)WHERE c = ?alone
The WHERE clause order in SQL does not matter — the planner reorders predicates. The index column order matters entirely.
Index column order is not stylistic. It is the query plan.
Rule of thumb: equality predicates first, in selectivity order within equalities; range predicates and ORDER BY columns last. (tenant_id, status, created_at) for WHERE tenant_id = ? AND status = ? AND created_at > ?. Not (created_at, tenant_id, status) when created_at is a range over a high-cardinality time column.
Seven composite index patterns teams get wrong
1. Range column leading the index
Leading with created_at or updated_at when the query uses a range (>, <, BETWEEN) forces the index to scan every row in the date window before applying tenant or status filters. Put the range column last after equality filters on high-selectivity columns.
2. Low-cardinality column first
Leading with status (five values) or is_active (boolean) narrows poorly. The index groups most of the table into few buckets. (tenant_id, status) beats (status, tenant_id) when both appear in queries — tenant usually filters more rows.
3. Indexing for OR conditions
WHERE tenant_id = 1 OR tenant_id = 2 may not use a composite index efficiently. WHERE tenant_id IN (1, 2) often behaves better. Separate indexes on OR branches sometimes beat one misordered composite. Check EXPLAIN — Bitmap Index Scans merging multiple indexes are valid.
4. Redundant single-column indexes
Index on (tenant_id, created_at) makes a standalone (tenant_id) index redundant for queries filtering on tenant_id. Drop the redundant index — it doubles write cost. Keep separate indexes only when query patterns genuinely diverge.
5. INCLUDE columns mistaken for access columns
CREATE INDEX ... INCLUDE (col) stores columns in leaf pages for index-only scans but does not make col searchable via the btree. Putting filter columns only in INCLUDE when they need to be index conditions produces index-only scans that still read the whole tenant partition.
6. Composite index for sort without filter prefix
ORDER BY created_at DESC alone needs (created_at DESC) — not (user_id, created_at DESC) unless queries always filter user_id first. The leading column must appear in the WHERE clause or the planner cannot reach the sort column efficiently.
7. Partial index opportunity missed
WHERE status = 'pending' ORDER BY total DESC LIMIT 50 on a table where 2% of rows are pending wants:
CREATE INDEX idx_pending_by_total
ON orders (total_amount_cents DESC)
INCLUDE (order_id, user_id, created_at)
WHERE status = 'pending';A full composite on (status, total_amount_cents) indexes 100% of rows when only 2% need indexing. Partial indexes are smaller, faster to maintain, and match the query — until the filter broadens to status IN ('pending', 'processing'), when a fuller composite may win.
EXPLAIN tells decoration from useful indexes
Run EXPLAIN (ANALYZE, BUFFERS) — not EXPLAIN alone. Buffer hits reveal real I/O pressure.
| Plan signal | Meaning |
|---|---|
Index Cond on leading columns | Index used as intended |
Filter on indexed columns after Index Scan | Wrong column order or missing prefix — rows fetched then discarded |
Seq Scan on large table | Index not eligible — missing leading column or poor selectivity |
Index Only Scan | Covering index hit — INCLUDE columns working |
Sort step after Index Scan | Sort column not in index order — reorder composite |
A Filter line on tenant_id beneath an Index Scan on created_at is the smoking gun for pattern #1. The index did work — the wrong work.
Row-Level Security compounds index mistakes. Policies that call auth.uid() per row turn already-marginal plans into disasters. Fix index order first, then apply RLS performance patterns — the planner fight is the same.
How should teams design Postgres composite indexes?
These questions prevent indexes that look correct in migration files and fail in production.
Does column order in the query matter?
No. Predicate order in SQL is irrelevant. Index column order is everything. Design indexes from query patterns, not from the order columns appear in WHERE clauses.
How many columns belong in one composite index?
Rarely more than three unless table usage is extremely stylized. Postgres documentation warns that wide composites are often unhelpful. Prefer targeted partial indexes over four-column general-purpose indexes that nothing uses efficiently.
When should you use separate indexes instead of one composite?
When queries filter on different column subsets without shared leading prefixes — (tenant_id) queries and (status, created_at) queries may need two indexes merged by Bitmap Scan rather than one composite that serves neither well.
A common argument runs the other way
The opposing view holds that storage is cheap and indexes should be added liberally — better to over-index than miss a slow query.
Write amplification disagrees. Every index updates on every insert and on indexed column updates. Redundant and misordered indexes slow writes, increase vacuum work, and confuse the planner with competing paths. On connection-pooled serverless workloads, slower writes compound latency across many short transactions.
Index intentionally. Measure with EXPLAIN ANALYZE. Drop indexes that production plans never use.
Key takeaways
- Postgres composite indexes follow leftmost-prefix rules — design from query patterns, not column lists.
- Equality columns first; range and ORDER BY columns last.
- Filter after Index Scan means wrong order — rows fetched then discarded.
- Partial indexes beat full composites when queries filter a small row fraction.
- Drop redundant single-column indexes subsumed by composites.
- INCLUDE enables index-only scans but does not make columns searchable.
- Verify with EXPLAIN (ANALYZE, BUFFERS), not index existence alone.
Conclusion
Composite indexes are not "add all the WHERE columns." They are btree sort orders that must align with how queries eliminate rows. The seven misuse patterns share one root cause: creating indexes from migration intuition instead of reading query plans.
The audit is simple: take the five slowest production queries, run EXPLAIN ANALYZE, check for Filter lines on indexed columns, reorder or replace indexes, measure again. The planner does not care about intentions. It cares about prefix order.