# 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.

```sql
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:

```sql
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:

```sql
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:

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4242;
```

```text
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
```

| Signal | What it means |
|---|---|
| `Index Searches: 1` | Ordinary single-descent index scan.  No skipping. |
| `Index Searches: N`, N ≈ distinct leading values | 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 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 →](https://app.dbgorilla.com/signup)

## 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.

## Related

- [Which PostgreSQL index type should I use?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/)
- [How to find missing indexes in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-missing-indexes-in-postgresql/)
- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)