B-Tree Skip Scan for Omitted Leading Columns #

You have an index on (region, sku) built for queries that filter by both. A new report filters only by sku. The standard advice has always been that the index is useless without its leading column, so the choice was a second index on sku — more storage, more write cost — or a sequential scan. PostgreSQL 18 changes that advice, but only for some leading columns.

The ordering rules that decide which queries a composite index serves are in multicolumn index column ordering. This page covers the skip scan exception to them.

The Planner Condition #

A B-tree on (region, sku) is sorted by region, then by sku within each region. A predicate on sku alone does not define a contiguous range of that ordering — matching entries are spread across every region.

Before PostgreSQL 18, the planner could still use the index for WHERE sku = 'A-1009', but only as a full index scan: read every leaf entry and test sku on each. That is only worthwhile when the index is much narrower than the table.

From PostgreSQL 18, the B-tree code can skip: it treats the omitted region column as if the query had said region = ANY(every distinct region), descends the tree once for each region, and reads only the entries for sku = 'A-1009' within it. The cost depends on the number of distinct leading values:

The planner estimates this from n_distinct of the leading column and chooses skip scan only when it is cheaper than the alternatives. Skip scan also applies when the leading column has a range condition rather than none, such as region > 'eu' with sku = ….

Full index scan versus skip scan A row of leaf segments grouped by region: apac, emea, latam, na. The top panel shows a full index scan reading every leaf entry from left to right. The bottom panel shows skip scan descending from the root separately into each region group and reading only the entries for the requested sku, jumping over the rest. before PG 18: full index scan apac …emea …latam …na … every entry read and tested PG 18: skip scan one descent per region → Index Searches: 4

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, sku, on_hand FROM stock_levels WHERE sku = 'A-1009';

PostgreSQL 18, 12 distinct regions:

Index Scan using stock_levels_region_sku_idx on stock_levels
    (cost=0.56..52.40 rows=12 width=24) (actual time=0.03..0.21 rows=12 loops=1)
  Index Cond: (sku = 'A-1009'::text)        -- no condition on region, yet an Index Cond on sku
  Index Searches: 13                        -- ~one descent per region (+1 to find the first)
  Buffers: shared hit=41
Execution Time: 0.24 ms

PostgreSQL 17, same query and index:

Seq Scan on stock_levels  (cost=0.00..412840.00 rows=12 width=24) (actual time=38.2..1410.6 rows=12 loops=1)
  Filter: (sku = 'A-1009'::text)
  Rows Removed by Filter: 24000000
  Buffers: shared hit=2204 read=172620
Execution Time: 1410.9 ms

The field to read: Index Searches. It should be close to the number of distinct leading values. If it approaches thousands, skip scan is spending most of its time descending the tree.

Recognising skip scan in the plan Three lines are annotated. Index Cond on sku alone with no region condition. Index Searches 13, roughly one per region. Buffers of 41 confirm only small parts of the index were read. Index Cond: (sku = …) no condition on leading region Index Searches: 13 one descent per distinct region Buffers: shared hit=41 only matching leaf pages read Index Searches near the leading column’s distinct count is healthy

Step-by-Step Resolution #

  1. Confirm the shape. The query constrains a later index column with equality or a range, and leaves the leading column unconstrained.

  2. Count distinct leading values.

    SELECT n_distinct FROM pg_stats
WHERE tablename = 'stock_levels' AND attname = 'region';

    Tens: skip scan is a strong candidate. Tens of thousands: expect a dedicated index to win.

  3. Measure on PostgreSQL 18 with EXPLAIN (ANALYZE, BUFFERS) and note Index Searches and buffers.

  4. Compare with a dedicated index, built without blocking writes, then drop the loser:

    CREATE INDEX CONCURRENTLY stock_levels_sku_idx ON stock_levels (sku);
EXPLAIN (ANALYZE, BUFFERS) SELECT region, sku, on_hand FROM stock_levels WHERE sku = 'A-1009';

  5. Decide on total cost, not the single query. A second index adds write amplification to every insert and update, and storage — see finding unused indexes safely. If skip scan is within a small factor of the dedicated index and the query is not latency-critical, keep one index.

Leading-column cardinality decides skip scan cost Three bars comparing skip scan buffers for a leading column with 12, 1,200 and 40,000 distinct values against a flat dashed line, labelled sku index, for a dedicated single-column index. At 12 values skip scan is close to the line. At 1,200 it is several times above. At 40,000 it is far above. 12 regions 1,200 stores 40,000 customers sku index bar height: buffers read by skip scan (relative)

Before and After #

-- BEFORE: PostgreSQL 17, index (region, sku), query on sku only
Seq Scan on stock_levels  Rows Removed by Filter: 24000000          Execution Time: 1410.9 ms

-- AFTER: PostgreSQL 18, same index, no schema change
Index Scan using stock_levels_region_sku_idx  Index Searches: 13    Execution Time: 0.24 ms

How the scan decides where to skip #

Skip scan does not first read the list of distinct regions and then search each one. It discovers them as it goes. The scan positions itself at the lowest possible region combined with sku = 'A-1009', reads matching entries, and when it reaches an entry whose sku has moved past the target, it uses the region value it is standing on to compute the next region to search for, then descends the tree again from the root to that position. Each descent costs a few page reads — the height of the tree — which is why the cost scales with distinct leading values and why Index Searches counts slightly more than the number of regions.

This also means skip scan adapts to the data in the index rather than to statistics. If the planner estimated 12 regions and there are 14, the scan still works correctly and the extra two descents are cheap. The statistics matter only for the decision to use skip scan at all; after that, the execution is driven by what the index contains.

Interaction with range conditions and ordering #

When the leading column has a range rather than no condition, skip scan restricts its descents to regions inside the range, which combines naturally with the partition-like layout of a composite index. Output order is by (region, sku), so a query with ORDER BY region can use the scan without a sort; a query ordering by another column still needs a sort above it.

Skip scan can also apply to a middle column. An index on (tenant_id, region, sku) queried with tenant_id = 7 AND sku = 'A-1009' can skip over region within that tenant. The same cardinality rule applies to the skipped column: few distinct regions per tenant is cheap, many is not. This makes skip scan relevant to the long-standing question in multicolumn index column ordering of whether a low-cardinality column belongs first — on PostgreSQL 18 it costs less to put it there, because queries that omit it are no longer locked out of the index.

Common Pitfalls #

Dropping a dedicated index after upgrading without measuring. Skip scan only helps low-cardinality leading columns. Diagnostic signal: Index Searches in the thousands after the drop. Fix: recreate the dedicated index with CREATE INDEX CONCURRENTLY.

Reordering index columns for skip scan. Putting a low-cardinality column first can hurt queries that relied on the old leading column for range scans and ordering. Diagnostic signal: new sorts appearing in other plans. Fix: evaluate all queries using the index, not just the one that prompted the change.

Stale n_distinct on the leading column. The planner decides from statistics; if it thinks there are 12 regions and there are 12,000 values, it will pick skip scan badly. Diagnostic signal: Index Searches far above the estimated distinct count. Fix: ANALYZE, or correct the estimate as in overriding n_distinct on large tables.

Expecting skip scan on non-B-tree indexes. It is a B-tree feature. Diagnostic signal: no change for GIN, GiST or BRIN indexes. Fix: use the index type’s own strategies.

Frequently Asked Questions #

Which PostgreSQL version has B-tree skip scan? PostgreSQL 18. Earlier versions can only read the whole index and test the later column on every entry.

What does Index Searches mean in EXPLAIN ANALYZE? The number of descents from the root. An equality lookup shows 1; a skip scan shows roughly one per distinct skipped value visited. It appears from PostgreSQL 18.

When is a separate single-column index still better than relying on skip scan? When the skipped leading column has many distinct values, because each costs a tree descent.

Up: B-Tree Index Optimization