PostgreSQL 18 B-tree Skip Scan: When Multicolumn Indexes Can Skip the Leftmost Column

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

Before PostgreSQL 18, a B-tree index on (region, customer_id) was usually a poor fit for WHERE customer_id = 7700 because the leading column had no equality condition. PostgreSQL 18 can use a skip scan: it repeatedly searches the index using internally generated values for the missing leading column.

Skip scan does not repeal the leftmost-prefix rule. It gives the planner another option when the leading column has few distinct values and a later condition can skip most leaf pages.

What You’ll Learn: how skip scan navigates a multicolumn index, build a lab, recognize the plan, understand the statistics that control the choice, and decide whether a dedicated index is still better.

1. The Core Example

CREATE TABLE orders
(
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    region      text NOT NULL,
    customer_id bigint NOT NULL,
    ordered_at  timestamptz NOT NULL,
    total       numeric(12,2) NOT NULL
);

CREATE INDEX ix_orders_region_customer
ON orders (region, customer_id);

A query containing both columns is naturally efficient:

SELECT *
FROM orders
WHERE region = 'EMEA'
  AND customer_id = 7700;

PostgreSQL 18 can also consider the same index for:

SELECT *
FROM orders
WHERE customer_id = 7700;

2. How Skip Scan Works

1. Read the statistics for the leading index column.
2. Generate a search such as region = APAC AND customer_id = 7700.
3. Reposition to the next distinct region and repeat.
4. Stop after all relevant leading-column groups have been searched.

If region has four values, four targeted searches can be cheap. If the leading column has hundreds of thousands of values, repeated searches approach a full index scan and the planner will generally choose another path.

3. Build a Data Distribution That Can Benefit

INSERT INTO orders(region, customer_id, ordered_at, total)
SELECT (ARRAY['APAC','EMEA','AMER','JAPAN'])[(g % 4) + 1],
       g % 250000,
       now() - (g % 365) * interval '1 day',
       10 + (g % 5000) / 10.0
FROM generate_series(1, 1000000) AS g;

ANALYZE orders;

4. Inspect the Plan

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT order_id, region, customer_id, ordered_at, total
FROM orders
WHERE customer_id = 7700;

Look for an index-based plan whose index condition uses the later column. Exact wording and costs vary by patch release and data distribution. The important evidence is the selected index, buffer reads, rows visited, and execution time—not whether the plan text contains a marketing label.

5. Confirm the Statistics

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 'orders'
  AND attname IN ('region','customer_id');

Stale or low-quality statistics can make the planner misjudge the number of leading-column groups or later-column selectivity. Run ANALYZE after material data changes and raise per-column statistics targets only when evidence justifies the extra analysis cost.

6. Compare With a Dedicated Index

CREATE INDEX ix_orders_customer
ON orders (customer_id);

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 7700;

7. Good and Bad Candidates

• Good: leading status, region, tenant tier, or type column with few distinct values.
• Good: highly selective predicate on a later index column.
• Good: read-heavy table where avoiding another index has meaningful write benefit.
• Poor: high-cardinality leading column.
• Poor: query returns a large percentage of rows.
• Poor: statistics are stale or the distribution is strongly correlated but not represented.

8. DBA Decision Checklist

1. Collect representative query frequencies and parameter values.
2. Run EXPLAIN (ANALYZE, BUFFERS) with and without the dedicated index in a safe environment.
3. Measure write cost and index size, not only one SELECT.
4. Confirm behavior after VACUUM/ANALYZE and realistic cache states.
5. Retain the dedicated index when its predictable latency is worth the maintenance cost.

Summary

Skip scan makes some multicolumn B-tree indexes useful for queries that omit leading equality predicates. It is most valuable when the missing leading column has few distinct values. Let the planner use it, but verify the real plan and compare it with a dedicated index before changing production indexing strategy.

Continue learning: To develop the tuning skills behind this index comparison, browse Udemy and search for PostgreSQL performance tuning courses with exercises on EXPLAIN, index design, and query optimization. For hands-on practice with PostgreSQL tables, schemas, and access privileges, explore DataCamp.

Related DBA park Guides

• PostgreSQL 18 Asynchronous I/O

Official References

• PostgreSQL 18: Multicolumn indexes
• PostgreSQL 18 release notes: skip scans
• PostgreSQL 18: Combining multiple indexes