Choose PostgreSQL multicolumn B-tree index order

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

“Put the most selective column first” is an incomplete rule for a multicolumn B-tree. A useful index order follows the query's access pattern: which columns have equality conditions, where a range starts, and whether the result must arrive in a particular order. Selectivity matters, but it does not replace that structure.

Consider a tenant-scoped event feed. Every request specifies one tenant and asks for the newest events. An index beginning with the tenant and continuing with the ordering columns can support both filtering and a short ordered page. Reversing the columns changes the work even if both indexes contain the same values.

Write the query before the index

Use PostgreSQL 18 and a disposable database. These examples create deterministic synthetic data; plans and runtimes depend on your environment.

CREATE TABLE tenant_event (
  id bigint PRIMARY KEY,
  tenant_id integer NOT NULL,
  occurred_at timestamptz NOT NULL,
  category text NOT NULL
);
INSERT INTO tenant_event
SELECT g, g % 100,
       timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second',
       CASE WHEN g % 5 = 0 THEN 'alert' ELSE 'activity' END
FROM generate_series(1,100000) AS s(g);

CREATE INDEX tenant_event_feed
ON tenant_event(tenant_id, occurred_at DESC, id DESC);
ANALYZE tenant_event;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, occurred_at
FROM tenant_event
WHERE tenant_id = 42
ORDER BY occurred_at DESC, id DESC
LIMIT 50;

Within a fixed tenant, the remaining index order matches the requested order. The id tie-breaker provides deterministic order if timestamps collide. This is also useful for keyset pagination, but the cursor must preserve both ordering values.

The example intentionally projects only columns in the index. If the application also returns category, the access pattern may involve heap reads or a covering design. Do not benchmark a narrow projection and apply the result to a wider endpoint without checking it.

Understand the first range boundary

For a conventional B-tree traversal, equality conditions on leading columns and a range condition on the next constrained column narrow the contiguous index region that must be scanned. Conditions on later columns can still be checked in the index, but may not reduce the initial range in the same way.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, occurred_at
FROM tenant_event
WHERE tenant_id = 42
  AND occurred_at >= timestamptz '2026-01-02 00:00:00+00'
ORDER BY occurred_at DESC, id DESC
LIMIT 50;

Now imagine adding category = 'alert'. An index on (tenant_id, category, occurred_at DESC, id DESC) can be attractive when both tenant and category are always fixed. For the unfiltered feed, however, category separates the timestamp streams. That index no longer directly supplies one global timestamp order within the tenant across all categories.

There is no contradiction. The two endpoints ask different questions and may justify different indexes, or a compromise based on their frequency and latency requirements.

Reference access model. Equality selects a prefix, the timestamp defines a range and order, and the ID resolves ties.

Do not teach an absolute leftmost-prefix myth

PostgreSQL 18 can use B-tree skip scan in suitable cases. A predicate on a later column may be served by repeated searches over distinct leading values when that is estimated to be worthwhile. This does not mean column order no longer matters, or that every missing leading predicate becomes cheap.

For an index on (category, occurred_at), a time predicate without category could be a candidate when the number of distinct categories is small. With a very large number of leading values, repeated searches may lose their appeal. Test the actual distribution and PostgreSQL version rather than repeating the absolute claim that the index is either fully usable or entirely unusable.

Likewise, a backward scan can reverse a B-tree's ordering, but it reverses the relevant ordering consistently. A request for one column ascending and another descending may need a mixed-direction index. Null ordering also belongs to the contract. Declare nullable sort behavior explicitly and test boundary values.

Evaluate the write side

Every index consumes storage and adds maintenance to inserts, updates, vacuum, and replication. Adding every permutation of three columns is rarely a good design. Start with the most important query families, identify overlapping prefixes and ordering requirements, and measure whether a smaller set covers the workload acceptably.

A partial index can reduce maintenance when only a stable subset matters. An included payload column can avoid some heap reads, but it widens the index and does not become a search key. A separate index on each predicate column can enable bitmap combinations, but bitmap access does not preserve B-tree ordering for a top-N query.

Compare candidates fairly

In a lab, test candidate indexes one at a time or explicitly record which indexes coexist. Keep the data distribution, statistics, session settings, and query parameters fixed. Include a large tenant, a small tenant, recent and old time ranges, and an empty result. Synthetic uniform tenant sizes can conceal the parameter skew that determines production performance.

Capture rows scanned, rows returned, sort behavior, buffers, and write overhead. If one candidate wins only because the test selects columns it happens to cover, state that limitation. The final decision should read like an explanation of the workload: “This index starts with tenant because every feed request fixes tenant, then matches the requested time and ID order.” That is more durable than a rule based on selectivity alone.

Sources and review scope

• Multicolumn indexes
• Indexes and ORDER BY
• Combining indexes