Composite Index Mastery: Column Order and the Leftmost Prefix

In one line

A composite index (a, b, c) is sorted by a, then b within a, then c. It serves queries that constrain a leftmost prefix of the columns, with equality columns first, then the sort column, then a range column. A range on an earlier column stops later columns from narrowing the scan.

0/5 · 0%

Think of it like this

A phone book sorted by surname, then first name, then city. Finding "Sharma, Amit" is instant; finding everyone named Amit (any surname) means reading the whole book; finding Sharmas whose first name starts with A–F, and then filtering by city, works but can't jump straight to the city.

Key ideas

01
For INDEX(a, b, c): a = ? ✓; a = ? AND b = ? ✓; a = ? AND b = ? AND c = ? ✓ (best); b = ? ✗ (no leftmost column; PostgreSQL 18's skip scan can help only when a has few distinct values); a = ? ORDER BY b ✓ (no sort step); a > ? AND b = ? partial: the range on a is used, but b is only a filter checked per entry.
02
The ESR rule: Equality columns first, then Sort columns, then Range columns. For WHERE tenant_id = ? AND status = ? AND created_at > ? ORDER BY created_at, use (tenant_id, status, created_at).
03
Among equality columns, order matters little for a single query; choose the order that lets other queries share the index (the column used alone elsewhere goes first). Selectivity matters less than the prefix rule.
04
Direction: (a, b DESC) matters only when mixing directions (ORDER BY a ASC, b DESC); a B-tree can be scanned backwards for a uniform reversal.
05
One composite index often replaces several single-column ones; PostgreSQL can combine single-column indexes with bitmap AND, but it's usually slower than one well-ordered composite.

Code & diagrams

composite.sqlsql

CREATE INDEX orders_tenant_status_created ON orders (tenant_id, status, created_at);

EXPLAIN SELECT * FROM orders
WHERE tenant_id = 7 AND status = 'open' AND created_at > now() - interval '1 day'
ORDER BY created_at LIMIT 50;
-- Limit
--   -> Index Scan using orders_tenant_status_created on orders
--        Index Cond: ((tenant_id = 7) AND (status = 'open') AND (created_at > ...))
-- (no Sort node: index order already matches ORDER BY)

EXPLAIN SELECT * FROM orders WHERE status = 'open';
-- Seq Scan on orders  Filter: (status = 'open')      <- leading column missing

composite-rules.txttext

INDEX (a, b, c)
WHERE a = ?                       -> seek on a
WHERE a = ? AND b = ?             -> seek on a, b
WHERE a = ? AND b = ? AND c = ?   -> seek on a, b, c
WHERE b = ?                       -> cannot seek (maybe skip scan in PG 18 if a has few values)
WHERE a = ? ORDER BY b            -> seek on a, rows already ordered by b
WHERE a > ? AND b = ?             -> seek on range of a, b checked as filter per entry
WHERE a = ? AND c = ?             -> seek on a, c checked as filter (b gap)

Interview problem

The problem

Design one index for three queries

Queries on ticket: (Q1) WHERE org_id=? AND status=? ORDER BY priority DESC, created_at (hot); (Q2) WHERE org_id=? AND assignee_id=? AND status=?; (Q3) WHERE org_id=? AND created_at BETWEEN ? AND ?. Propose a minimal index set.

When it breaks

Range column placed before equality columns

What you see

(created_at, tenant_id) for WHERE tenant_id = ? AND created_at > ? scans every tenant's recent rows and filters, which is slow for large time windows.

Fix & prevent

Reorder to (tenant_id, created_at): equality first, range last.

Explain it without notes

Why does a range predicate on the first column limit the usefulness of later columns?

Practice

Which of these can use INDEX(a,b): ORDER BY a, b; ORDER BY b; WHERE a IN (1,2) ORDER BY b?

Trade-offs

↔
Wider composite indexes serve more queries but are larger and costlier to maintain.

Done when you can

I can order composite index columns by the ESR rule and predict which queries use them.

Command Palette