Do I still need the leading column? Multicolumn indexes and skip scan
Quick answer: Mostly yes, but PostgreSQL 18 relaxed the rule. Its new B-tree skip scan lets a multicolumn index be used when the leading column has no equality constraint, by repeatedly searching for each distinct leading value. It only pays off when that column has few distinct values. On PostgreSQL 17 and earlier the leftmost-prefix rule still applies in full.
What was the leftmost-prefix rule?
Section titled “What was the leftmost-prefix rule?”Every tutorial written before late 2025 says the same thing: an index on
(a, b, c) is only usable if your query constrains a. Filter on b alone and
the index is dead weight.
That follows from how a B-tree stores keys. Entries are sorted by a first,
then b, then c, like a phone book sorted by surname, then first name. If you
know the surname you can jump to a narrow slice of pages. If you only know the
first name, there is no single slice to jump to; the matching entries are
spread across the entire book.
The PostgreSQL documentation still states the underlying rule precisely:
Equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will always be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they’ll always save visits to the table proper, but they do not necessarily reduce the portion of the index that has to be scanned.
Trailing-column predicates were never ignored. They were applied inside the index to avoid heap fetches. They could not reduce how much of the index had to be read, which is usually where the cost is.
What changed in PostgreSQL 18?
Section titled “What changed in PostgreSQL 18?”PostgreSQL 18 (released 25 September 2025) added skip scan for B-tree indexes. The release note reads:
Allow skip scans of btree indexes. This allows multi-column btree indexes to be used in more cases such as when there are no restrictions on the first or early indexed columns (or there are non-equality ones), and there are useful restrictions on later indexed columns.
Instead of one index search, the executor performs roughly one search per
distinct value of the skipped column. Given an index on (status, customer_id)
and a query that only filters customer_id, PostgreSQL enumerates the distinct
status values it finds in the index and runs the equivalent of status = 'pending' AND customer_id = 4242, status = 'shipped' AND customer_id = 4242, and so on.
Roughly, because the count comes out above the cardinality rather than equal to it: four distinct values measured nine searches on 18.6. Treat the model as a description of the work rather than an arithmetic prediction.
CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, status text NOT NULL, -- 4 distinct values customer_id bigint NOT NULL, -- millions of distinct values created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX orders_status_customer_idx ON orders (status, customer_id);
-- No predicate on the leading column:SELECT * FROM orders WHERE customer_id = 4242;On PostgreSQL 17 and earlier that query gets a sequential scan, or a full index scan if the planner thinks the index is narrow enough to read end to end. On PostgreSQL 18, skip scan can serve it with a handful of index searches instead: measured below, nine of them against four distinct statuses.
What does skip scan not fix?
Section titled “What does skip scan not fix?”Skip scan does not remove the need to consider column order.
Flip the index around:
CREATE INDEX orders_customer_status_idx ON orders (customer_id, status);
SELECT * FROM orders WHERE status = 'shipped';Now the skipped leading column is customer_id, with a distinct value for
almost every row, and the skip has nothing to skip to.
That does not mean millions of searches. Measured on 18.6 with
customer_id leading, that query planned a sequential scan at 74.7 ms; forced
onto the index with enable_seqscan = off it reported Index Searches: 1 and
read 735 index pages, which is the whole index. Instead of attempting a descent per value, the executor gives up skipping and scans, which is what the documentation says:
This approach is generally only taken when there are so few distinct
xvalues that the planner expects the scan to skip over most of the index (because most of its leaf pages cannot possibly contain relevant tuples). If there are many distinctxvalues, then the entire index will have to be scanned, so in most cases the planner will prefer a sequential table scan over using the index.
Three limits matter:
- It is cost-based, not guaranteed. The planner decides. Bad statistics on
the leading column, such as a stale
n_distinct, can push it the wrong way in either direction.ANALYZEmatters more than it used to. - A rescued index is still more work than the right index. Measured on the
table above, the skip scan did 9 searches and touched 33 buffers. Adding
(customer_id, status)and rerunning the same query did 1 search and touched 7. Both answered in well under a millisecond, so the win here is pages read rather than time, and it is pages read that grows with the table. Skip scan makes the wrong index usable; it does not make it as cheap as the right one. - B-tree only. GIN, GiST, BRIN and hash are unaffected.
How do I check whether my server can do this, and whether a query benefits?
Section titled “How do I check whether my server can do this, and whether a query benefits?”First, the version:
SHOW server_version;
SELECT current_setting('server_version_num')::int >= 180000 AS skip_scan_available;Then look at the plan. PostgreSQL 18 also added an Index Searches counter to
EXPLAIN ANALYZE output. It reports how many times the index was searched by
that node.
Measured on 18.6, filling the orders table above with 400,000 rows across four
statuses and 100,000 customers:
INSERT INTO orders (status, customer_id)SELECT (ARRAY['pending','paid','shipped','cancelled'])[(i % 4) + 1], (i % 100000) + 1FROM generate_series(1, 400000) i;
ANALYZE orders;EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)SELECT * FROM orders WHERE customer_id = 4242;Index Scan using orders_status_customer_idx on orders (actual time=0.021..0.041 rows=4.00 loops=1) Index Cond: (customer_id = 4242) Index Searches: 9 Buffers: shared hit=33| Signal | What it means |
|---|---|
Index Searches: 1 | Ordinary single-descent index scan. No skipping. |
Index Searches above 1, and well below the index’s page count | Skip scan is doing the work. |
Index Cond names only trailing columns | The leading column was skipped, not constrained. |
| Seq Scan despite a matching index | Planner judged the index too expensive, usually because the query is not selective enough. |
Do not expect the count to equal the number of distinct values. The leading column here has exactly four, and the counter reads nine. It is the number of descents the executor made, not the cardinality, and it runs somewhat above it. Read it as “many more than one, far fewer than the whole index” rather than as a number you can predict.
One caveat: a count above 1 is not proof of skip scan on its own. Scans driven
by an IN (...) list or an array operator also perform multiple searches. Read
it together with the Index Cond.
What is the practical column-ordering guidance now?
Section titled “What is the practical column-ordering guidance now?”The guidance is mostly unchanged, with one new option.
- Order by predicate shape, not by “selectivity” folklore. Columns with equality constraints go first; the one column you use with a range or inequality goes last among the constrained ones. On PostgreSQL 17 and earlier, everything after that first inequality stops narrowing the scan; on 18, skip scan can still use it when the range column has few distinct values.
- If two access patterns differ only by a low-cardinality prefix, one index
may now cover both.
(status, customer_id)on PostgreSQL 18 can serve both “by status and customer” and “by customer alone”. Pre-18 that needed two indexes. Fewer indexes means cheaper writes. - Never put a high-cardinality column first and hope.
(customer_id, …),(user_id, …),(created_at, …)as a skipped prefix is not rescuable. - Design for the version you actually run. Most production estates are not on the newest major. PostgreSQL 14, 15, 16 and 17 are all still supported and none of them have skip scan; on those, the leftmost-prefix rule holds completely and an index whose leading column you never filter is dead weight.
Why does AI-generated code get this wrong?
Section titled “Why does AI-generated code get this wrong?”There are two failure modes, and they pull in opposite directions.
The common one: your agent was trained on a decade of “the leading column is mandatory” material and will confidently tell you an index is unusable when your PostgreSQL 18 server would happily skip-scan it, so it proposes a redundant second index you did not need.
The emerging one: an agent that has read a PG18 announcement overcorrects to
“column order does not matter any more” and puts customer_id first in an index
meant to serve status lookups. That index will never be skip-scanned.
Both mistakes have the same root cause: the agent cannot see your server version,
your n_distinct for the leading column, or the plan the planner actually
chooses. Those three facts settle the question in seconds and are unavailable
to a model reasoning from text alone.
How do I stop it coming back?
Section titled “How do I stop it coming back?”- Pin the assumption: put your major version in the repo’s
CLAUDE.md/.cursorrulesso agents stop guessing. - Review
EXPLAIN (ANALYZE, BUFFERS)for new index suggestions before creating them, and checkIndex Searcheson PG18. - Keep statistics fresh on low-cardinality leading columns; skip-scan costing
depends on
n_distinct. - Before adding an index, check whether an existing multicolumn index already covers the pattern; on PG18 more of them do.
Does a multicolumn index still require a predicate on the leading column?
On PostgreSQL 17 and earlier, effectively yes: without an equality constraint on the leading column the planner cannot narrow the index scan, so it usually falls back to a sequential scan. PostgreSQL 18 added B-tree skip scan, which can use the index anyway by performing a separate index search for each distinct leading-column value.
What version of PostgreSQL added B-tree skip scan?
PostgreSQL 18, released 25 September 2025. The release notes describe it as allowing multi-column btree indexes to be used in more cases, such as when there are no restrictions (or only non-equality ones) on the first or early indexed columns and there are useful restrictions on later columns.
Does skip scan mean index column order no longer matters?
No. Skip scan works by repeating the index search once per distinct value of the skipped leading column, so it is only cheaper than a sequential scan when that column has very few distinct values. With a high-cardinality leading column the whole index would have to be read, and the planner will normally choose a sequential scan instead.
How can I tell whether a query used a skip scan?
Run EXPLAIN (ANALYZE, BUFFERS). PostgreSQL 18 reports an Index Searches counter on each index scan node, and a skip scan shows more than one. Do not expect it to equal the distinct count: measured on 18.6, a leading column with four distinct values reported nine searches. Note that other plans, such as an IN list, also report multiple searches.
Should I reorder my existing indexes now?
Only where you have a redundant pair that differs by a low-cardinality prefix, because PostgreSQL 18 may let one index replace both. Do not reorder working indexes speculatively.