PostgreSQL 18 Skip Scan: What It Changes for Multicolumn Indexes

PostgreSQL 18 adds more planner support for skip scan lookups on multicolumn B-tree indexes. That sounds small, but it changes an everyday indexing question: can an index still help when a query does not constrain the first column?

Before PostgreSQL 18, many teams learned a simple rule of thumb: put equality filters first in a multicolumn B-tree index, because predicates on later columns are much less useful when the leading column is missing. That rule is still a good design instinct. The news is that PostgreSQL 18 can sometimes navigate a multicolumn index by repeatedly probing distinct values of a missing prefix column, reducing the amount of index work compared with reading the whole index or falling back to a sequential scan.

This is not a reason to delete carefully designed indexes after an upgrade. It is a reason to rerun representative query plans, especially for SaaS tables where one index may serve several dashboard, admin, or background-job access patterns.

What changed in PostgreSQL 18

The PostgreSQL 18 release notes call out "skip scan" lookups that allow multicolumn B-tree indexes to be used in more cases. The official multicolumn index documentation explains the mechanism: PostgreSQL can generate a dynamic equality constraint internally for a missing leading column, then perform repeated searches through the index when the planner estimates that this is cheaper than scanning a larger portion of the structure.

In practical terms, imagine an index on (tenant_id, status, created_at). A tenant-scoped query such as WHERE tenant_id = $1 AND status = 'open' is still the cleanest fit. A cross-tenant operations query such as WHERE status = 'open' AND created_at > now() - interval '1 day' omits the leading tenant_id. PostgreSQL 18 may be able to use skip scan if the distinct tenant count, predicate selectivity, and cost model make repeated index probes attractive.

The planner remains cost-based. Skip scan is not a promise that every suffix predicate suddenly becomes fast. It is an additional access strategy that can improve the middle ground between "perfect index prefix" and "index is mostly useless."

Where skip scan helps most

Skip scan is most interesting when the omitted prefix column has a small enough number of distinct values and the later column predicate is selective enough to justify repeated probes. The PostgreSQL documentation gives an example where a multicolumn index can be searched efficiently because the leading column has few distinct values while a later column has many.

For application teams, that means the feature is likely to show up in the places where schemas already contain natural grouping columns: region, status bucket, plan tier, soft-delete flag, event type, or another low-cardinality value. It may also help some operational queries on multitenant systems, but only when the tenant count and data distribution make the math work.

The key is selectivity. If PostgreSQL must perform thousands or millions of internal probes, skip scan can lose to a more direct index, a bitmap plan, or a sequential scan. If it only needs a small number of probes to reach a highly selective suffix condition, it can avoid building another narrow index for an occasional query.

What not to change after the upgrade

Do not treat skip scan as a replacement for index design. The strongest multicolumn B-tree indexes still match the query shapes your application runs constantly. For high-volume endpoints, the normal left-prefix discipline remains valuable: equality columns that sharply reduce the search space should appear before range columns, and sort requirements should be considered when they are part of the response path.

Skip scan is better viewed as a safety net and consolidation tool. It can make secondary access patterns less painful, especially when adding another index would increase write amplification, vacuum work, storage use, and migration risk. That matters for managed PostgreSQL users because every extra index is operational state: it consumes disk, slows writes, appears in backups, and has to be rebuilt or maintained during some schema changes.

It also does not remove the need for statistics. The planner chooses skip scan from estimates. If table statistics are stale, if a column has skewed values, or if a development database has only a few rows, the plan you see may not match production. Run ANALYZE after large data changes and evaluate plans against realistic distributions before making index decisions.

A practical upgrade review

After moving a workload to PostgreSQL 18, start with queries that were previously awkward but not important enough to deserve their own index. Good candidates are admin filters, reporting queries, queue inspection screens, support tooling, and periodic jobs that filter on the second or third column of an existing index.

Use EXPLAIN (ANALYZE, BUFFERS) on representative parameters. Look for an index scan or index-only scan that reports skip scan behavior in the plan details, then compare total time, shared buffer reads, and rows removed by filters. A faster plan on a tiny sample is not enough. You want to know whether the plan stays stable for common parameter values and for the worst customer, largest tenant, busiest status, or widest time range.

If skip scan makes an occasional query acceptable, you may be able to avoid adding a new index. If the query is latency-sensitive or runs constantly, prefer a purpose-built index and measure write overhead separately. If the query only exists for support operations, an occasional skip scan win can be enough.

How this affects managed PostgreSQL planning

For managed PostgreSQL teams, the operational value is mostly in review timing. A major version upgrade is a good moment to rerun slow-query analysis because planner behavior changes can make old index advice incomplete. ArmorDB already includes PgBouncer for connection management; for query performance, the same practical rule applies: measure the database behavior your application actually produces rather than relying on a generic checklist.

If you are preparing a move to PostgreSQL 18, add skip scan to the upgrade checklist alongside extension compatibility, query plan regression testing, and backup restore validation. For teams still on PostgreSQL 17, this is a feature to watch rather than a reason to rush. The important work is collecting enough slow-query history now that the PostgreSQL 18 comparison is meaningful later.

Takeaway

PostgreSQL 18 skip scan makes multicolumn B-tree indexes more flexible, especially when queries filter on later columns and the missing leading column has a manageable number of distinct values. It does not overturn index design fundamentals. Keep purpose-built indexes for hot paths, but use the PostgreSQL 18 upgrade window to find places where skip scan can reduce duplicate indexes or improve occasional operational queries.

Sources and further reading

• PostgreSQL 18 release notes
• PostgreSQL 18 release announcement
• PostgreSQL documentation: multicolumn indexes
• PostgreSQL documentation: using EXPLAIN