Composite Indexes — Column Order & the Leftmost-Prefix Rule

A composite index is a single B-tree sorted across several columns at once — like a phone book sorted by (last_name, first_name). That single sorting fact decides the most consequential design choice in indexing: the order of the columns, which determines which queries the index can serve at all.

Intermediate composite-index leftmost-prefix column-order equality-before-range Created 2026-06-03 · Updated 2026-06-03

Key Components

Composite (multicolumn) index: A single B-tree built over two or more columns. Rows are sorted by the first column, then by the second within each first-column value, and so on — one structure, not several.
Leftmost-prefix rule: An index on (A, B, C) can efficiently serve a query only if the query constrains a leading prefix of its columns: A, or A, B, or A, B, C — never B or C in isolation.
Equality-before-range: The dominant column-ordering rule: columns matched by equality (=) should precede columns matched by a range (>, <, BETWEEN). A range "uses up" the index ordering, so every column after it can only be filtered, not seeked.
Selectivity: How many rows a predicate eliminates. A more selective equality column (e.g. user_id) narrows the scan faster than a low-selectivity one (e.g. is_deleted). It is a tiebreaker, secondary to query patterns.
Bitmap & skip scans: Fallbacks when the leading column is missing: a bitmap scan combines results from separate single-column indexes; a skip scan loops over each distinct leading value — only worthwhile when that column has few distinct values.

Concrete Example

Create one index across two columns, then ask which queries it can serve:

CREATE INDEX ON employees (last_name, first_name);

-- usable:  WHERE last_name = 'Adams'                          (leading col)
-- usable:  WHERE last_name = 'Adams' AND first_name = 'Bob'   (full key)
-- NOT efficiently usable:
--          WHERE first_name = 'Bob'   (leading col missing → scattered → Seq Scan)

The reason becomes obvious once you picture the actual leaf order the index produces:

Adams, Alice
Adams, Bob
Baker, Alice    <- first_name ordering RESTARTS within each last_name
Baker, Carl
Clark, Dan

first_name is only sorted within a single last_name. Globally the second column is scattered — every "Alice" is spread across the whole index. That scattering is the root cause of every rule that follows: there is no contiguous run of rows to seek to when you only know first_name.

The practical consequence: a composite (a, b) also doubles as a usable index on a alone (its prefix), so a separate INDEX (a) is usually redundant and should be dropped. But it does nothing for WHERE b = …; that needs its own INDEX (b). The design question is never "which columns" but "what order, given the actual query patterns."

Visual Model

Think of the index as a phone book sorted by (last, first). You can look someone up by last name, or by last + first — but never by first name alone, because the first names are scattered across every page. Walk through the query shapes below and watch which contiguous band of rows the index can actually seek to.

Step 1 of 5

The pattern is consistent: a query can seek to a contiguous run of leaves only when it pins down a leading prefix. The moment a column is missing or a range opens, ordering breaks and the index can only filter, not seek.

Deeper — Edge Cases & Gotchas

Choosing column order — three rules

Rule 1 — Equality columns before range columns (the big one). Consider WHERE status = 'active' AND created_at > '2026-01-01':

Index	Verdict	What happens
`(status, created_at)`	good	Seek `status='active'`, then walk the `created_at` range within that status. Both columns narrow.
`(created_at, status)`	bad	The range on the leading column opens a wide date span; `status` is scattered within it → it degrades to a filter, not a seek.

Mechanism: a range scan "uses up" the index ordering. Every column after a range column can only be filtered. Put range columns last among the columns you want to narrow on.

Rule 2 — Most-frequently-queried column leads. If half your queries don't filter on A, then A shouldn't lead — those queries can't use the index at all.

Rule 3 — Selectivity as a tiebreaker. Among equality columns, put the more selective one first (e.g. user_id over is_deleted) so the scan narrows faster. This is secondary to query patterns.

Anti-pattern: a range in the middle silently kills the tail

Anti-pattern: assuming every column in the index narrows the scan, even after a range.

CREATE INDEX ON events (a, b, c);

-- query:
WHERE a = 1 AND b > 5 AND c = 9;
--    a → seek      (equality on leading col)
--    b → range     (walk b > 5 within a = 1)
--    c → FILTER    (the b-range already broke ordering → c can't seek)

Once the range on b opens, the rows are no longer in c-order, so c is evaluated row-by-row as a filter, not a seek. If the equality on c is the more important filter, reorder to (a, c, b) so c can seek and b's range stays last.

Other traps

Redundant-prefix trap. Having both INDEX (a) and INDEX (a, b) usually makes (a) dead weight — the composite already serves WHERE a = … via its prefix. Audit and drop the standalone.
ORDER BY must match the prefix. INDEX (a, b) satisfies ORDER BY a, b and WHERE a = … ORDER BY b for free — but not ORDER BY b, a.
Don't over-index. Indexes beyond ~3 columns rarely help unless usage is extremely stylized. Each extra column adds write cost and on-disk size.
When patterns are unpredictable, several single-column indexes combined by a bitmap scan can beat one rigid composite. A skip scan (Postgres 18+, long in Oracle) can rescue WHERE b = 7 on (a, b) by looping over each distinct a — but only pays off when a has few distinct values; otherwise it degrades to a full scan.

Test Yourself

Given INDEX (a, b, c), which query can the index not use efficiently?

For WHERE tenant_id = 42 AND created_at > now() - interval '7 days', which column order is correct?

A teammate adds INDEX (user_id) alongside an existing INDEX (user_id, created_at). What's the problem, and what should you do?

On INDEX (a, b, c) with WHERE a = 1 AND b > 100 AND c = 5, which columns narrow the scan and which becomes a filter — and why?