Multicolumn Index Column Ordering in PostgreSQL #

A composite B-tree index is not a set of columns the planner can use in any combination — it is an ordered structure, and that order determines which predicates can drive an efficient seek and which are relegated to a post-scan filter. Get the column order right and multiple predicates collapse into a single Index Cond that also satisfies your ORDER BY. Get it wrong and the same index degrades to a Filter, or is ignored entirely in favor of a sequential scan.

The governing rule is simple to state and easy to misapply: equality columns first, then the range or sort column. This page grounds that rule in real EXPLAIN output. It lives under B-Tree Index Optimization; once your predicates land as Index Cond, see building effective covering indexes to also serve the select list from the index.

The Leftmost-Prefix Rule #

A B-tree stores entries sorted by the first column, then by the second within each first-column value, and so on — exactly like sorting a spreadsheet by column A, then column B. This has one decisive consequence for query planning:

A column can be used as an index access predicate (an Index Cond that narrows the range of the index scanned) only if every column to its left is constrained by an equality predicate.

An equality on a leading column pins the scan to one contiguous slice of the index. Within that slice, the next column is sorted, so a further equality or range on it also seeks efficiently. But the moment a leading column is not pinned by equality — because the query does not mention it, or constrains it only by a range — the columns after it are scattered throughout the index and can no longer drive a seek.

Composite index usability by query shape A table showing an index on tenant_id, status, created_at against three query shapes. Filtering on tenant_id and status uses both leading equalities as Index Cond and returns created_at ordered. Filtering on tenant_id alone seeks on tenant_id and can still order by created_at only partially. Filtering on status alone cannot seek because the leading tenant_id column is unconstrained, so status becomes a Filter. Index: (tenant_id, status, created_at) query predicate index behaviour tenant_id = ? AND status = ? ORDER BY created_at Index Cond on both; created_at pre-sorted, no Sort tenant_id = ? Index Cond on tenant_id; seeks the leading slice status = ? (no tenant_id) cannot seek: leading col free status → Filter or Seq Scan A column seeks only when every column to its left is pinned by equality

The Rule in EXPLAIN Output #

Assume an events table with an index built equality-first:

CREATE INDEX idx_events_tenant_status_created
  ON events (tenant_id, status, created_at);

Query A — both leading columns pinned by equality. This is the shape the index was built for:

EXPLAIN
SELECT * FROM events
WHERE tenant_id = 42 AND status = 'OPEN'
ORDER BY created_at;
Index Scan using idx_events_tenant_status_created on events
  Index Cond: ((tenant_id = 42) AND (status = 'OPEN'))   -- ← both equalities seek
                                                          -- created_at returned in order, no Sort node

Both equality predicates become Index Cond, the scan touches only the matching slice, and because created_at is the next index column, rows already emerge sorted — the planner needs no separate Sort.

Query B — leading column pinned, trailing sort satisfied. Filtering on just tenant_id still seeks:

EXPLAIN SELECT * FROM events WHERE tenant_id = 42;
Index Scan using idx_events_tenant_status_created on events
  Index Cond: (tenant_id = 42)     -- ← leading equality seeks the tenant slice

Query C — the anti-pattern: filtering on a non-leading column alone.

EXPLAIN SELECT * FROM events WHERE status = 'OPEN';
Seq Scan on events
  Filter: (status = 'OPEN')        -- ← status is NOT the leading column; no seek possible
  Rows Removed by Filter: 940000

Because tenant_id (the leading column) is unconstrained, the index cannot seek to status values — they are scattered across every tenant’s slice. The planner abandons the index and scans the whole table, applying status as a Filter. This is the single most common multicolumn-index mistake: expecting a non-leading column to be usable on its own. If you need to query status alone, it needs its own index or the leading position.

Placing the Range or Sort Column #

The rule’s second half is about the last useful column. Consider WHERE status = 'OPEN' ORDER BY created_at, served by an index leading with the equality column:

CREATE INDEX idx_events_status_created ON events (status, created_at);

EXPLAIN
SELECT * FROM events WHERE status = 'OPEN' ORDER BY created_at;
Index Scan using idx_events_status_created on events
  Index Cond: (status = 'OPEN')    -- ← equality pins the slice
                                    -- within it, created_at is already sorted → no Sort

The equality on status pins one contiguous slice, and within that slice created_at is stored in order, so the ORDER BY is free. Reverse the column order to (created_at, status) and it breaks: created_at leads, so status = 'OPEN' can no longer seek, and the query falls back to filtering. A range or ORDER BY column must always sit after the equality columns, never before them.

Step-by-Step: Choosing the Order #

Step 1 — Classify every column in the query #

For the target query, label each column: equality-tested (col = ?), range-tested (col > ?, BETWEEN, LIKE 'x%'), or used only in ORDER BY.

Step 2 — Put equality columns first #

All equality columns go at the front. Their relative order among themselves does not affect usability (any equality-prefixed column still seeks), though leading with the most selective one keeps the index tidy for other queries.

Step 3 — Put the range or sort column last #

Exactly one range-or-sort column can benefit from the index after the equalities. Place it next, in the direction the query orders by:

CREATE INDEX idx_events_tenant_status_created
  ON events (tenant_id, status, created_at DESC);   -- DESC to match ORDER BY created_at DESC

Step 4 — Verify predicates land as Index Cond #

Run EXPLAIN and confirm each intended predicate appears in the Index Cond line, not the Filter line. A predicate in Filter means it did not drive the seek — revisit the ordering.

Step 5 — Confirm the Sort node is gone #

If the query has an ORDER BY that the index should satisfy, verify no Sort node appears above the Index Scan. Its presence means the sort column is not correctly positioned or the direction mismatches.

Step 6 — Drop useless leading columns #

If the leading column has one or two distinct values (a low-cardinality flag) and is not needed by other queries, it may add index bloat without selectivity. Re-evaluate whether it earns the leading position.

Before/After Plan Comparison #

-- BEFORE: index (created_at, status) — range/sort column placed first
Seq Scan on events
  Filter: (status = 'OPEN')            -- ← status cannot seek behind leading created_at
  Rows Removed by Filter: 940000

-- AFTER: index (status, created_at) — equality first, sort column last
Index Scan using idx_events_status_created on events
  Index Cond: (status = 'OPEN')        -- ← equality seeks; created_at pre-sorted, no Sort

Swapping the two columns turns a full-table Filter scan into a targeted Index Cond that also eliminates the Sort.

Common Pitfalls #

Range column before the equality column. Leading with created_at when you filter equality on status destroys the index’s usability for that predicate — the equality can no longer seek. The range or sort column must always come last.

ORDER BY direction mismatch. An index on (status, created_at ASC) satisfies ORDER BY created_at ASC and, by backward scan, ORDER BY created_at DESC — but a mixed order like ORDER BY status ASC, created_at DESC needs the directions baked into the index (created_at DESC) or a Sort returns.

An unnecessary low-cardinality leading column. Leading with a two-value flag like is_active forces every query to constrain it by equality to reach the columns behind it, and adds little selectivity. Consider a partial index on the common flag value instead.

Assuming column order does not matter because “the index covers all the columns.” Coverage of the select list is separate from access: an index can contain a column and still be unable to seek on it if it is not correctly prefixed by equalities. The distinction between an index that only covers and one that also seeks is what separates sequential from index scans.

Frequently Asked Questions #

What order should columns go in a composite B-tree index? Put equality-tested columns first, then the range or ORDER BY column last. A B-tree can use a column as an index access predicate only if every column to its left is constrained by equality. So (tenant_id, status, created_at) serves WHERE tenant_id = ? AND status = ? ORDER BY created_at efficiently — the leading equalities seek, and the trailing column returns rows already sorted.

Why is my second index column showing up as a Filter instead of an Index Cond? Because a column to its left is not constrained by equality. A B-tree can only seek on a column when all preceding columns are pinned by equality. If you query WHERE status = ? on an index leading with tenant_id, the index cannot seek to status, so PostgreSQL applies it as a Filter after reading rows rather than as an Index Cond.

Can a multicolumn index satisfy ORDER BY without a Sort node? Yes, if the equality-constrained leading columns come first and the ORDER BY column is the next index column in a compatible direction. The index then returns rows already in order and the planner drops the Sort. If the ORDER BY direction cannot be met by a forward or backward scan, a Sort reappears.

Related