Composite Indexes

~12 min · indexes, composite

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

One index, many columns

A composite (multi-column) index is a single B-tree where keys are concatenated. CREATE INDEX ... ON orders (customer_id, placed_at DESC) sorts first by customer_id, then by placed_at within each customer. This is what makes "give me Alice's most recent 5 orders" instant.

Column order matters

A composite index on (a, b, c) can serve queries filtering on:

a alone
a + b
a + b + c

It cannot serve queries filtering only on b or only on c — those need their own indexes. The leading column is the gatekeeper.

The classic shape: equality first, range last

For WHERE customer_id = 7 AND placed_at >= '2026-04-01', the optimal composite is (customer_id, placed_at) — equality first, range last. Reverse it and the planner can't use both columns to narrow the range.

Code

Composite indexes·sql

-- Serves: WHERE customer_id = ?  /  WHERE customer_id = ? AND placed_at > ?
-- Does not serve well: WHERE placed_at > ? alone
CREATE INDEX orders_customer_placed_idx ON orders (customer_id, placed_at DESC);

-- ORDER BY can come along for free
SELECT id, total FROM orders
WHERE  customer_id = 7
ORDER  BY placed_at DESC
LIMIT  5;
-- Index Scan on orders_customer_placed_idx — no separate sort step.

Index intersection with two single-column indexes·sql

-- Sometimes two single-column indexes suffice; the planner combines them
-- via Bitmap And. Slower than a perfect composite but more flexible.
CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX orders_status_idx   ON orders (status);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 7 AND status = 'paid';
-- Plan: Bitmap Heap Scan on orders + BitmapAnd of two indexes.

External links

Use The Index, Luke — Concatenated Indexes

Exercise

Pick a query that filters on two columns. Try a composite index in each column order; compare EXPLAIN. Then try two single-column indexes; compare again. Decide which the planner prefers and why.