PostgreSQL 18 Skip Scan: Multicolumn Index Examples

Create a table where skip scan can be useful

create table orders (
  id bigint generated always as identity primary key,
  region text not null,
  created_at timestamptz not null,
  total numeric(12, 2) not null
);

create index orders_region_created_at_idx
  on orders (region, created_at);

analyze orders;

explain (analyze, buffers)
select id, region, created_at, total
from orders
where created_at >= now() - interval '1 day'
order by created_at desc;

Populate enough representative rows and realistic region values before comparing plans. Tiny development tables usually favor a sequential scan.

Why the leading column still matters

A B-tree on (region, created_at) is naturally efficient for a query with region = 'us-east' and a created_at range. The index can jump directly to one contiguous portion of the tree.

Without a region condition, PostgreSQL 18 can sometimes search once for each region value and apply the created_at condition within each group. That is attractive when there are only a few regions and the date range returns a small fraction of rows.

Do not look for a Skip Scan node

The plan can still be labeled Index Scan or Index Only Scan. Skip scan describes how PostgreSQL uses the B-tree internally. Use the chosen index, index conditions, actual rows, loops, execution time, and buffer counts to judge it.

Compare with enable_indexscan disabled only as a diagnostic experiment, never as an application setting. Planner switches help you understand alternatives; they are not a durable tuning strategy.

Compare against a non-index plan in one transaction

begin;
set local enable_indexscan = off;
set local enable_indexonlyscan = off;

explain (analyze, buffers)
select id, region, created_at, total
from orders
where created_at >= now() - interval '1 day';

rollback;

Know when another index is better

If region has thousands of distinct values, repeated probes can cost more than a sequential scan. If the created_at query is central to the application, an index beginning with created_at is more direct and easier to reason about.

Every additional index increases storage and write work. Compare query frequency and latency improvement with insert, update, vacuum, and cache costs before keeping both indexes.

Statistics decide whether the option is visible

The planner needs a credible estimate of distinct leading values and result selectivity. Run ANALYZE after loading representative data. Raise a column's statistics target only when the default sample does not describe important skew well enough.

Extended statistics can help with correlated columns, but they do not make every multicolumn index shape correct. Keep the query, data distribution, and index order in the same investigation.

Save the before-and-after plans

Capture PostgreSQL 17 and 18 plans for the same restored dataset, or compare the candidate indexes on PostgreSQL 18. The EXPLAIN ANALYZE guide explains how to read row estimates, loops, and buffers without treating every sequential scan as a failure.