Skip to main content

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?​

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 comes straight 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 smeared 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.

Note the nuance that gets lost in most blog posts: trailing-column predicates were never ignored. They were applied inside the index to avoid heap fetches. What they could not do was reduce how much of the index had to be read, which is usually where the cost is.

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.

The mechanism is not magic and it helps to say it out loud: instead of one index search, the executor performs 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.

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 be worth reading end to end). On PostgreSQL 18, skip scan can serve it with four index searches.

What does skip scan not fix?​

This is the part that will get mangled as the feature spreads. Skip scan is not permission to stop thinking about 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 millions of distinct values. "One index search per distinct value" means millions of searches, worse than reading the table. The documentation is explicit about the boundary:

This approach is generally only taken when there are so few distinct x values 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 distinct x values, 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 more limits worth internalising:

  • It is cost-based, not guaranteed. The planner decides. Bad statistics on the leading column (a stale n_distinct) can push it the wrong way in either direction. ANALYZE matters more than it used to.
  • A rescued index is still slower than the right index. Four index searches beat a sequential scan, but one search on (customer_id, status) beats both. Skip scan raises the floor; it does not raise the ceiling.
  • 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?​

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, which is exactly the signal you need, it reports how many times the index was searched by that node:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4242;
Index Scan using orders_status_customer_idx on orders
      (actual time=0.041..0.058 rows=3.00 loops=1)
  Index Cond: (customer_id = 4242)
  Index Searches: 4
  Buffers: shared hit=14
SignalWhat it means
Index Searches: 1Ordinary single-descent index scan. No skipping.
Index Searches: N, N ≈ distinct leading valuesSkip scan is doing the work.
Index Cond names only trailing columnsThe leading column was skipped, not constrained.
Seq Scan despite a matching indexPlanner judged skipping too expensive, usually a high-cardinality leading column.

One honest 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?​

Unchanged in spirit, with one new degree of freedom.

  1. 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. Everything after that first inequality stops narrowing the scan.
  2. 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, a real win.
  3. Never put a high-cardinality column first and hope. (customer_id, …), (user_id, …), (created_at, …) as a skipped prefix is not rescuable.
  4. 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?​

Two failure modes now, pulling 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 cheerfully 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?​

  • Pin the assumption: put your major version in the repo's CLAUDE.md / .cursorrules so agents stop guessing.
  • Review EXPLAIN (ANALYZE, BUFFERS) for new index suggestions before creating them, and check Index Searches on 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.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the facts this decision actually turns on: the server version you are running, the existing multicolumn indexes and their column order, the distinct-value statistics on the leading column, and the real plan for the query. That is the difference between an agent quoting a 2019 blog post and one telling you whether your index can be skip-scanned. It surfaces and explains through your agent; it does not create indexes or change your schema. Get started free →

FAQ​

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 scan and usually falls back to a sequential scan. PostgreSQL 18's skip scan can use the index anyway, by running a separate index search for each distinct leading-column value.

What version added B-tree skip scan? PostgreSQL 18, released 25 September 2025.

Does skip scan mean column order no longer matters? No. It costs one index search per distinct value of the skipped column, so it only wins when that column has very few distinct values. A high-cardinality leading column still defeats it, and the planner will prefer a sequential scan.

How do I tell whether a query used a skip scan? EXPLAIN (ANALYZE, BUFFERS). PostgreSQL 18 reports Index Searches: N per index scan node; a skip scan shows more than one, roughly matching the distinct leading values. IN lists also report multiple searches, so read it alongside the Index Cond.

Should I reorder my existing indexes now? Only where you have a redundant pair that differs by a low-cardinality prefix. PG18 may let one index replace both. Do not reorder working indexes speculatively.