How should we order columns in a composite index for filters and sorting?

Put columns first in the order your dominant query can use them, not by a blanket “most selective first” rule. For PostgreSQL B-tree indexes, the default design rule is to lead with equality predicates, then the first range predicate, and then any columns needed to satisfy ORDER BY if that ordering is part of the same query shape. This works because PostgreSQL says multicolumn B-tree indexes are most efficient with constraints on leading columns, and that equality constraints on leading columns plus an inequality on the first column without equality will always limit the scanned portion of the index (PostgreSQL multicolumn indexes). Matching ORDER BY can also avoid a separate sort, which is especially valuable with LIMIT (PostgreSQL indexes and ORDER BY).

That said, this is the baseline rule, not an absolute law. PostgreSQL also documents B-tree skip scan, where the planner can sometimes use later-column constraints to reduce how much of the index it reads even when an earlier column lacks a conventional equality constraint (PostgreSQL multicolumn indexes). Treat that as an optimization the planner may choose, not as the starting point for index design.

What rule should you actually use?

For a B-tree composite index, ask four questions in order:

1. Which query family matters most?
2. Which predicates are equality predicates?
3. Where is the first range predicate such as >, <, or BETWEEN?
4. Does the query also need rows in a specific order, often with LIMIT?

The core PostgreSQL rule is straightforward: as a rule, equality constraints on leading columns plus an inequality on the first column without equality are what most directly narrow the scanned portion of the index (PostgreSQL multicolumn indexes). That is why WHERE tenant_id = ? AND status = ? AND created_at > ? usually wants (tenant_id, status, created_at), not (created_at, tenant_id, status).

How does sorting change the column order?

Sorting matters when you want the index to both filter and return rows in the requested order. PostgreSQL can use a B-tree index to deliver sorted output and skip an explicit sort step; this is especially valuable for ORDER BY ... LIMIT n queries because the database can stop early (PostgreSQL indexes and ORDER BY).

That means ordering columns for filtering alone is not always enough. If your common query is:

WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20

then (tenant_id, status, created_at DESC) is usually the right starting point. The equality columns come first, and the trailing created_at DESC matches the required output order inside each filtered group.

Which index should you choose for a feed query?

Suppose you have an events table and two common candidates:

• CREATE INDEX ON events (tenant_id, status, created_at DESC);
• CREATE INDEX ON events (created_at DESC, tenant_id, status);

For the query:

SELECT * FROM events WHERE tenant_id = 42 AND status = 'open' ORDER BY created_at DESC LIMIT 50;

prefer (tenant_id, status, created_at DESC).

Why? tenant_id and status are equalities, so they should lead. created_at is then well placed to provide the ordering for the already-filtered subset. With (created_at DESC, tenant_id, status), PostgreSQL can read rows in time order, but for this query family the later columns are not the primary way to narrow the scan up front under the usual multicolumn B-tree rule (PostgreSQL multicolumn indexes). In some cases the planner may find a better-than-naive path with skip scan, but that is still the exception rather than the main design pattern (PostgreSQL multicolumn indexes).

The reverse index becomes attractive only if your dominant query is closer to “latest events globally” or “latest events by a broad time window first, with weaker filtering afterward.”

Can one index serve every query?

Usually not. Composite indexes are strongest for the query families that constrain their leading columns. PostgreSQL notes that multicolumn indexes are most efficient with constraints on leftmost columns, and wider indexes also add space and write cost (PostgreSQL multicolumn indexes).

Use this checklist:

• Put stable equality filters first.
• Put the first range column next.
• Add sort columns only when that query shape is common enough to justify the index.
• Create a second index when another important query starts from a different leading predicate.
• Keep indexes narrow unless a real query plan justifies more columns.

Follow-up Q&A:

Should the most selective column always go first? No. In PostgreSQL B-tree indexes, leftmost equality and the first range condition are the default design rule; generic selectivity alone is not.

What about mixed sort directions? If you need ORDER BY x ASC, y DESC, define the multicolumn index with that mixed ordering; a plain (x, y) cannot produce every mixed variant by scan direction alone (PostgreSQL indexes and ORDER BY).

Next step: take your top three slow queries, group them into query families, and design indexes from their shared WHERE plus ORDER BY shape before adding anything broader.

Reviewed: 2026-09-06