Designing PostgreSQL Composite Indexes for Filters and Sorting

A composite B-tree index stores multiple columns together in a defined order. A B-tree is PostgreSQL’s default index type; it keeps entries sorted by their key values. PostgreSQL can use it to find rows that match a query’s conditions and return them in key order. It supports checks for one exact value, checks for a span of values, and ordered results. The index helps when a query filters on multiple columns and sorts the matching rows in an order that follows the index keys.

Start with a query that runs often, then order the index keys to match the columns it filters and sorts. PostgreSQL can then find matching rows and return them in order, sometimes without a separate sort.

Put equality filters before the sort key

Suppose a service lists the newest open tickets for one customer:

SELECT id, created_at, subject
FROM tickets
WHERE customer_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

A matching index is:

CREATE INDEX tickets_customer_status_created_idx
    ON tickets (customer_id, status, created_at DESC);

The first two keys are the columns the query checks for one exact value. Within the entries for that customer and status, created_at orders tickets from newest to oldest. PostgreSQL can use the index to find the matching rows in the requested order, and with a suitable plan it can stop after finding 20. The planner chooses a plan based on the query and data; the index does not guarantee that PostgreSQL will avoid a sort.

Why the leftmost keys matter

A multicolumn B-tree sorts entries by its first key, then by its second key within each value of the first, and so on. Because the keys are ordered from left to right, the first columns determine which part of the index PostgreSQL can search efficiently.

When the query checks the first keys for exact values, PostgreSQL can narrow its search. It can narrow the search further when it checks the next key against a span of values rather than one exact value. PostgreSQL can also check conditions on later keys, but those conditions generally do not reduce the portion of the index it scans. For example, an index on (customer_id, status, created_at) fits a query filtering by customer_id, or by both customer_id and status. It is generally a weaker fit for a query filtering only by status, because that column is not first.

PostgreSQL 18 also documents B-tree skip scan. In some cases, the planner searches the index repeatedly to use a condition on a later key, even when the query does not restrict an earlier key. Whether this helps depends on the data and the planner’s estimates. Skip scan qualifies the leftmost-key rule in some cases, but key order still matters.

Match the requested sort direction

A B-tree index can return rows in ORDER BY order when its keys and their directions match the requested order. PostgreSQL can scan an index from the start or the end. Scanning backward reverses the sort direction of every key together.

For example, an index on (priority, created_at) supports ordering by both columns ascending with a forward scan, or both descending with a backward scan. It does not directly provide ORDER BY priority ASC, created_at DESC. If that mixed order is a common query pattern, define it in the index:

CREATE INDEX tickets_priority_created_idx
    ON tickets (priority ASC, created_at DESC);

The order of keys matters when sorting as well as searching. If a query fixes customer_id and status to single values, the remaining created_at key can provide the requested order. If a preceding key is not fixed, later keys sort rows within each group of that key, which may not produce the global order the query requests.

Compare a composite index with separate indexes

Separate indexes can serve different kinds of queries, such as searches by customer_id alone and searches by status alone. PostgreSQL can combine indexes to find rows that match several filters. PostgreSQL marks matching rows in a bitmap and visits the table rows in physical order, so it does not preserve index order; a query with ORDER BY may still need a separate sort.

A composite index can directly support a query that uses its leading keys and sort order. Each additional index also takes storage and adds work when PostgreSQL changes table rows. PostgreSQL advises using multicolumn indexes sparingly and notes that indexes with more than three columns are unlikely to help unless table usage is unusually specialized. Use that as a reason to justify each key, not as a strict column limit.

Check the plan with representative data

Create the index, update the table statistics PostgreSQL uses to estimate query costs, and inspect the query plan:

ANALYZE tickets;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, subject
FROM tickets
WHERE customer_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Check whether PostgreSQL uses the index and whether the plan includes a separate Sort step. Compare execution on representative data: when a query reads a large share of a table, a sequential scan followed by a sort can cost less than an index scan. EXPLAIN ANALYZE runs the query, so use care with statements that modify data.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com