Topic: PostgreSQL

PostgreSQL 18 Skip Scan: The Leftmost Rule, Revisited

PostgreSQL 18 can reuse a multicolumn B-tree when the leftmost column is not filtered. The win is real, but cardinality and query plans still decide.

The usual advice for a multicolumn B-tree is easy to remember: column order matters. An index on (status, created_at) is great for queries that constrain status, but much less useful for queries that only constrain created_at.

PostgreSQL 18 added a useful exception. Its skip scan optimization can generate values for a missing leading condition and perform repeated searches on the same index. That lets some queries use a later column without reading the entire index.

A recent r/PostgreSQL discussion about overlooked index behavior included a developer who had removed extra indexes after skip scan proved useful. That is a good reason to investigate the feature, but not a reason to start dropping indexes.

The practical rule is narrower:

Skip scan makes an existing composite index useful in more cases. It does not make column order irrelevant.

What PostgreSQL is actually skipping

Consider an order table with this index:

CREATE INDEX orders_status_created_at_idx
ON orders (status, created_at);

The main dashboard filters both columns:

SELECT id, status, created_at
FROM orders
WHERE status = 'pending'
  AND created_at >= now() - interval '1 hour';

This is the ordinary leftmost-prefix case. PostgreSQL navigates directly to status = 'pending', then scans the relevant created_at range.

Now an operational report omits status:

SELECT id, status, created_at
FROM orders
WHERE created_at >= now() - interval '5 minutes';

Before PostgreSQL 18, the (status, created_at) index was often a poor fit. The useful created_at entries are scattered across every status group, so the planner might prefer a sequential scan or require a separate index on created_at.

With skip scan, PostgreSQL can behave roughly as if it issued several searches:

status = 'pending'   AND created_at >= ...
status = 'paid'      AND created_at >= ...
status = 'cancelled' AND created_at >= ...
status = 'refunded'  AND created_at >= ...

Those predicates are not added to the SQL. The B-tree code generates the missing equality internally, repositions to the next status group, and skips leaf pages that cannot contain a recent row.

The PostgreSQL 18 release notes describe the same boundary: skip scan helps when early indexed columns have no restriction, or only non-equality restrictions, while later columns do have useful restrictions.

The omitted column must be cheap to enumerate

Skip scan is attractive when the missing leading column has few distinct values. Four statuses mean roughly four targeted searches. That can be much cheaper than reading a large table or walking every page of a large index.

Replace status with customer_id, and the arithmetic changes. Repeating a search for hundreds of thousands of customers is not a shortcut; it is a full scan with extra B-tree navigation. The official documentation says the planner will usually prefer a sequential scan when there are too many distinct leading values.

Selectivity on the later predicate matters too. A five-minute time range may touch a tiny slice of the table. A one-year range may return most rows, at which point sequential I/O can be the sensible plan even if an index exists.

This is why “PostgreSQL can use any column in a composite index now” is technically tempting and operationally wrong. It can consider more paths. The cost model still decides whether a path is worth taking.

Skip scan does not appear as a special plan node

The execution plan may say Index Scan or Index Only Scan; it does not need to contain a node literally named Skip Scan.

PostgreSQL 18 added a more useful clue. EXPLAIN ANALYZE reports Index Searches for B-tree scans. The documentation’s skip-scan example uses an index whose first column has four values and reports three searches for the requested range.

A realistic check is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders
WHERE created_at >= now() - interval '5 minutes';

Look at the whole result:

• Index Searches: repeated navigation is evidence that matters.
• Buffers: the plan should avoid a meaningful amount of I/O, not merely mention an index.
• Estimated versus actual rows: a large mismatch suggests weak statistics or skew.
• Execution time and loops: the improvement must survive the complete plan.
• Heap fetches for an index-only scan: the visibility map still affects whether it stays index-only.

Do not prove the feature by setting enable_seqscan = off and celebrating the remaining plan. That can show whether an index path exists, but it cannot show that the path is cheaper.

Check statistics before changing the index set

Skip scan depends on the planner estimating how many leading values it must visit. That estimate comes from table statistics, not from the schema declaration.

Start with the leading column:

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'orders'
  AND attname = 'status';

Then confirm that autovacuum has recent statistics, or run ANALYZE in an appropriate maintenance window after a large data change. Testing against an empty staging database proves very little because the planner is solving a different problem.

PostgreSQL 18 also made optimizer statistics portable through pg_dump --statistics-only. A recent daily.dev post on reproducing production query plans highlights the feature, and the official pg_dump documentation defines its limits. It is useful for a sanitized test environment, but restored statistics remain an approximation and can be overwritten by ANALYZE.

Should the separate created_at index go away?

Maybe, but “the planner used the composite index once” is not enough evidence.

Keeping only (status, created_at) saves the storage, WAL, cache pressure, vacuum work, and write amplification of another index. Skip scan can make that consolidation reasonable when:

• status has low cardinality and changes slowly.
• Queries on created_at alone are selective but not the dominant workload.
• Measured latency remains inside the service budget.
• The composite index is already necessary for common status-and-time queries.

A dedicated (created_at) index still earns its place when:

• The leading column has many distinct values.
• The later-column query is frequent or latency-sensitive.
• The query returns enough rows that repeated searches add visible cost.
• The smaller single-column index stays hotter in cache.
• It provides an ordering or covering path the composite index cannot replace cheaply.

The PostgreSQL guide to combining indexes makes the trade-off explicit. A composite index may serve both query shapes when its leading column has no more than several hundred distinct values, but a separate later-column index can still be reasonable. Creating every possible combination is only defensible when reads dominate and all paths are genuinely common.

My default after upgrading is therefore conservative: keep the existing indexes, capture representative plans and latency, then remove one redundant index at a time. The rollback is a normal CREATE INDEX CONCURRENTLY, but rebuilding a large production index is still slower than keeping it for one observation window.

A five-step evaluation

1. Confirm the query runs on PostgreSQL 18 or newer; older versions cannot demonstrate this optimization.
2. Inspect the distinct-value distribution of every omitted leading column.
3. Refresh representative statistics and use production-shaped parameters.
4. Compare EXPLAIN (ANALYZE, BUFFERS) across narrow and broad predicates; record Index Searches, buffers, rows, and time.
5. Remove a separate index only after workload-level evidence shows the write savings exceed the read regression risk.

There is no need to redesign a healthy index set just because skip scan exists. Its best use is often quieter: after an upgrade, it can turn an occasional query from a full scan into a respectable plan and make one marginal duplicate index unnecessary.

Conclusion: the leftmost rule became cost-aware, not obsolete

PostgreSQL 18 did not abolish B-tree ordering. It taught the planner a new way to navigate around a missing prefix.

When the omitted leading column has few values and the later predicate is selective, repeated searches can skip most of the index. When the leading column has high cardinality or the query wants a large part of the table, the same technique loses its advantage.

Treat skip scan as an opportunity to measure and simplify, not as permission to stop designing indexes. The planner gained another path; the workload still chooses the winner.

External references

• PostgreSQL 18: Multicolumn Indexes
• PostgreSQL 18 release notes
• PostgreSQL 18: Using EXPLAIN
• PostgreSQL 18: Combining Multiple Indexes
• PostgreSQL 18: pg_dump
• daily.dev: Production query plans without production data
• Reddit: Things you didn’t know about indexes