Composite Indexes

On the last page, we saw that every index costs writes, disk, and RAM. So we want fewer indexes that each do more work. A composite index does exactly that.

The Problem: One Column Isn't Enough

The "My Orders" page in our food delivery app doesn't just find a user's orders. It shows delivered orders, newest first, 20 at a time:

SELECT * FROM orders
WHERE user_id = 42 AND status = 'delivered'
ORDER BY created_at DESC
LIMIT 20;

We only have an index on user_id. Say user 42 has 500 orders, 300 of them delivered:

1Index on user_id only
2  → find user 42's 500 orders        (fast, index)
3  → read all 500 rows                (check status on each)
4  → keep 300 delivered               (filter)
5  → sort 300 by created_at           (extra sort step)
6  → return 20

The index helped with the first step only. The database still reads, filters, and sorts hundreds of rows for every page view.

What if one index could handle the filter and the sort?

What a Composite Index Is

A composite index (also called a multi-column index) is one index built on several columns, in a fixed order.

Think of a phone book. It is sorted by last name, then by first name inside each last name. A composite index works the same way: sorted by the first column, then by the second inside each first value, and so on.

CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, created_at);

Here is how the entries are sorted inside this index:

1(user_id, status,     created_at)
2(41,      delivered,  2026-10-25)
3(42,      cancelled,  2026-09-14)
4(42,      delivered,  2026-10-01)   ┐
5(42,      delivered,  2026-10-20)   │ all of user 42's delivered orders
6(42,      delivered,  2026-10-26)   ┘ sit together, already sorted by date
7(42,      pending,    2026-10-27)
8(43,      delivered,  2026-10-02)

Now let's run our query against it.

How the Query Uses It

All of user 42's delivered orders sit next to each other, already in date order. The database jumps to the end of that group and reads backwards:

1Composite index (user_id, status, created_at)
2  → jump to (42, delivered, newest)   (B-tree lookup, ~3 page reads)
3  → read 20 entries in order           (no filter, no sort)
4  → fetch those 20 rows
5  → return 20

The second plan stays fast even for a customer with 10,000 orders.

But couldn't we just create three separate indexes instead?

Composite vs Separate Indexes

With separate indexes on user_id, status, and created_at, the database usually picks one index and checks the other conditions by reading rows. PostgreSQL can sometimes combine indexes (a bitmap scan), but that is still slower than one composite index and still needs a sort.

The composite index is faster and cheaper on writes here. Its catch is the last row of the table: it only helps queries that match its column order.

Column Order Matters

Our index (user_id, status, created_at) is like the phone book sorted by last name, then first name:

1Query on user_id                       → works (like finding "Sharma")
2Query on user_id + status              → works (like finding "Sharma, Rahul")
3Query on user_id + status + created_at → works
4Query on status only                   → does NOT work (like finding every "Rahul")

The entries for status = 'delivered' are scattered across every user, so the index can't jump to them. This is the leftmost prefix rule, and it's important enough to get its own page.

Interview Angle

Common mistakes:

• Thinking three single-column indexes equal one composite index.
• Picking column order randomly. Order decides which queries the index can serve.

Summary

1Composite index → one index on several columns, in a fixed order
2Sorted like     → phone book: first column, then second inside it
3Our query       → user + status + newest first → 20 rows read instead of 500
4vs separate     → faster, and 1 index update per write instead of 3
5Catch           → only helps queries that start from the leftmost columns

Next, we'll look closely at the leftmost prefix rule: exactly which queries a composite index can and can't help.