What Is a Composite Index?

Quick answer
A composite index is an index on more than one column, such as (customer_id, created_at).
It can serve any query that filters or sorts by a leftmost prefix of its columns — the leading columns — so order is the design decision.
(customer_id, created_at) serves queries filtering by customer_id, or by customer_id plus created_at. It does not serve a query filtering only by created_at.
One composite index can replace several single-column indexes, which saves write overhead as well as storage.
Put the equality-filtered column first and the range or sorted column last; that combination both filters and avoids a sort.

What is a composite index?

A composite index, also called a multi-column index, stores a sorted structure keyed on several columns together, in a fixed order. Conceptually it is like a phone book sorted by surname and then by first name: you can look up by surname alone, or by surname and first name, but not by first name alone. That property — usable from the left — is why the order of the columns is the important part of the definition.

The database can use the index for a query that constrains a leftmost prefix of the columns. It can also use trailing columns to avoid sorting, provided the earlier columns are constrained by equality. This is why a well-chosen composite index often removes both a scan and a sort at the same time, which a pair of single-column indexes cannot do.

How column order decides which queries are served

Think of the index as sorted first by column one, and within equal values of column one, by column two. A query that provides a value for column one can jump to that section; one that also provides a value for column two can narrow within it. A query that provides only column two has to search the whole index, so the planner usually falls back to a different access path.

The practical rules follow from that. Columns compared with equality go first, because they narrow to a contiguous range. A column used for range comparison or sorting comes after them, because a range on an earlier column ends the usefulness of the ones that follow. And a column that is only ever used alone does not belong in the middle of a composite index; it would be better as its own single-column index or as the leading column.

-- The index
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

-- Served: equality on the leading column, sorted by the second.
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- Served: equality on both columns.
SELECT id, total FROM orders
WHERE customer_id = 42 AND created_at > now() - interval '30 days';

-- NOT served by this index: no constraint on customer_id,
-- so the leading column provides nothing to jump to.
SELECT id FROM orders WHERE created_at > now() - interval '1 day';

When a composite index earns its keep

Three situations. First, a query that filters by one column and sorts by another: a composite index in the right order satisfies both, whereas a single-column index on the filter forces a sort afterwards. Second, a query that filters by two columns that are commonly used together, which is the classic lookup such as tenant plus id. Third, replacing several overlapping single-column indexes, which reduces the write cost of maintaining them.

The counter-case is just as clear. If two columns are always filtered independently, two single-column indexes serve both queries and the composite serves only one. If the columns have very low cardinality individually, a composite index on them can be almost useless because the index does not discriminate enough rows to be worth reading. And if the query only ever filters on the second column, put that column first in its own index.

How to choose the order

Start from the queries you actually run, written down, not from the schema. For each query, list the equality filters, then the range filters and sorts. The index order is then: all the equality columns first, ordered so the most selective combination leads, followed by the range or sort column. If two common queries need different orders, they need two indexes, and that is a real cost in write throughput that should be accepted knowingly.

Verify rather than assume. An index that exists but is never chosen is pure overhead, so run the query plan and confirm the index is used, then remove the ones that are not. Composite indexes interact with the planner in ways that are hard to predict from the column list alone — the only reliable check is the plan, before and after.

Where this bites vibecoders
Generated schemas usually contain one index per foreign key or none at all, and hand-added indexes are almost always single-column. The result is a query that filters by two columns and sorts by a third, doing a scan plus a sort when a single composite index would have done both. The fix requires reading the query, not the schema, which is why it is missed: the schema looks reasonable while the access pattern that matters is invisible from it.

Where AI coding assistants get this wrong

Adding an index per column instead of one composite index that matches the query.
Putting the range or sort column first, which stops the equality columns being useful.
Creating several overlapping composite indexes and paying their write cost.
Assuming a composite index helps a query that only constrains a trailing column.
Never checking the plan to confirm the index is used.
Indexing low-cardinality columns in combination and expecting selectivity.

Checklist

Write down the actual queries and their filters and sorts.
List the equality columns first, then the range or sort column.
Create one composite index per distinct access pattern, not per column.
Confirm the planner uses it, before and after.
Remove indexes the planner never chooses.
Re-measure write throughput if you add several indexes to a write-heavy table.

FAQ

Does column order matter in a composite index?

It determines which queries the index can serve, so yes — it is the central design decision. An index on (a, b) supports filtering by a and by a plus b. It does not efficiently support filtering by b alone, because the structure is ordered by a first. Choosing the order backwards makes the index useless for the query it was created for.

Can a composite index replace two single-column indexes?

Often, if the two queries both constrain the leading column plus the other. If the queries are independent — one filters only on a, another only on b — the composite index serves the first and not the second, so both indexes are still needed. Fewer indexes is a benefit, not a rule.

How many columns should a composite index have?

Usually two or three, chosen to match real queries. Adding columns increases the index size and the write cost, and beyond the columns a query actually constrains the extra columns provide no benefit unless they turn it into a covering index. Design for the access pattern rather than for the schema’s shape.

What about ordering, ascending or descending?

Match the index direction to the query’s sort direction. For a query that sorts descending, an index defined with the leading equality column ascending and the sort column descending avoids the sort entirely. Mismatched direction can force the planner to sort the result even though it used the index.