Skip to content

Why is Postgres 18 not using skip scan on my composite index?

Last updated

View as Markdown

Quick answer: Skip scan is a plan the planner chooses based on cost; no setting turns it on. PostgreSQL 18 may use it when repositioning among the values of an unconstrained index column costs less than the alternatives, which usually means there are few such values. Read the plan with EXPLAIN (ANALYZE, BUFFERS) and costs left on, because Index Searches alone does not tell you what happened.

Every number on this page was measured on two containers. A difference between them reflects the version, not the machine.

serversPostgreSQL 18.6 in container dbg-pgskip, PostgreSQL 17.11 in container dbg-pgskip17
imagespostgres:18.6 and postgres:17.11, defaults, Docker on an 8 vCPU host
table400,000 rows, a int, b int, c int, index on (a, b), 2,163 table pages
date2026-09-20

Plan shape, buffer counts and timings depend on the machine, the cache, the settings and the statistics. The results below come from one environment. The same SQL will not necessarily produce them elsewhere.

A B-tree on (a, b) is ordered by the whole key, with a first and then b within each a. Rows sharing a b value are spread across the a groups, so b = 42 on its own does not name one contiguous stretch of the index.

That never made the index unusable, only unbounded. The PostgreSQL 18 documentation puts the rule this way:

A multicolumn B-tree index can be used with query conditions that involve any subset of the index’s columns, but the index is most efficient when there are constraints on the leading (leftmost) columns.

That sentence is from Multicolumn Indexes in the PostgreSQL 18 documentation, read 2026-09-20.

You can see the earlier behavior. The same table and query on PostgreSQL 17.11 in container dbg-pgskip17, 2026-09-20, chose the index and read 337 buffers of a 338-page index, five runs out of five. It used the index but had to read nearly all of it.

PostgreSQL 18 added skip scan. The documentation describes the mechanism rather than a plan label:

Skip scan works by generating a dynamic equality constraint internally, that matches every possible value in an index column (though only given a column that lacks an equality constraint that comes from the query predicate, and only when the generated constraint can be used in conjunction with a later column constraint from the query predicate).

That is from Multicolumn Indexes in the PostgreSQL 18 documentation, read 2026-09-20. Two details follow from it: the skipped column does not have to be the leading one, and nothing in the plan output says “Skip Scan”.

PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, 400,000 rows and a leading column with four distinct values:

DROP TABLE IF EXISTS ss;
CREATE TABLE ss (a int, b int, c int);
INSERT INTO ss SELECT i % 4, i % 1000, i FROM generate_series(1, 400000) i;
CREATE INDEX idx_ss_ab ON ss (a, b);
VACUUM ANALYZE ss;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ss WHERE b = 42;
Aggregate (cost=30.04..30.05 rows=1 width=8) (actual time=0.078..0.078 rows=1.00 loops=1)
Buffers: shared hit=19
-> Index Only Scan using idx_ss_ab on ss (cost=0.42..29.04 rows=397 width=0) (actual time=0.020..0.054 rows=400.00 loops=1)
Index Cond: (b = 42)
Heap Fetches: 0
Index Searches: 6
Buffers: shared hit=19
Execution Time: 0.092 ms

Leave the costs on while diagnosing. The estimate of 397 rows against 400 actual is how you tell whether the planner’s picture matches the table, and the estimated cost is its stated reason for the choice. The same query on PostgreSQL 17.11 in container dbg-pgskip17 on 2026-09-20 estimated the same node at 4356.40 rather than 29.04.

Index Cond: (b = 42) has no condition on a, so the index is doing work for a column that is not its leading column.

Index Searches: 6 means six descents rather than one scan. That is consistent with a skip scan here, but it does not prove one by itself, as the next section shows.

Heap Fetches: 0 shows that this plan is also an index-only scan, a separate mechanism that happens to apply. Skip scan does not require it.

Index Searches is a counter, not a signature

Section titled “Index Searches is a counter, not a signature”

The PostgreSQL 18 documentation defines the line as a total:

Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes) show an “Index Searches” line that reports the total number of searches across all node executions/loops:

That is from Using EXPLAIN in the PostgreSQL 18 documentation, read 2026-09-20.

Ordinary index work produces it too. Three queries against a plain single-column primary key on a 400,000 row table, with no composite index and no skip scan possible, PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20:

queryIndex Searchesbuffer hits
WHERE id = 4214
WHERE id IN (42, 4242, 42424, 242424)416
WHERE id IN (42, 43, 44, 45)14

The same four-value IN list gives four searches or one depending on where the values sit. The counter tells you how many descents happened, but it does not identify a skip scan by itself. A skip scan has a B-tree index whose leading column is absent from the Index Cond, with more searches than the predicate asks for.

It is not confined to the leading column. On an index over (a, b, c) with the same 400,000 rows, a holding four values and b holding a hundred, PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20:

predicateIndex Searchesbuffers
a = 2 AND c = 7733hit=67 read=35
c = 77126hit=284 read=97

The first query constrains the leading column and skips the middle one. The relevant count is the number of groups it repositions among, not the cardinality of column a.

The same query and the same 400,000 rows, varying only how many distinct values the leading column holds. PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, each row warmed and then read three times in fresh connections. The index measured 338 pages in every row, from pg_relation_size('idx_sw') / current_setting('block_size')::int:

distinct values in aIndex Searchesbuffer hitsexecution time, three runs (ms)
46250.190, 0.197, 0.224
1012430.191, 0.224, 0.260
50511600.291, 0.298, 0.477
1001013100.427, 0.458, 0.559
2002016100.680, 0.689, 0.720
5003341,0091.116, 1.134, 1.486
10003341,0091.286, 1.307, 1.710

The following results apply only to this table on this machine.

The search count tracks the number of groups, one per value and a couple over, until it stops.

It plateaus at 334, and the cost does not fall. At 500 distinct values and at 1000, the plan did the same 334 searches and touched the same 1,009 buffers on that PostgreSQL 18.6 container on 2026-09-20. The plateau is not a property of the query.

At the plateau, 1,009 buffer hits against a 338-page index is not a saving. Those are not distinct pages, as the next section explains. At the plateau the scan did more buffer work than PostgreSQL 17.11 did reading an index of that size end to end, which was 337 hits on that PostgreSQL 17.11 container on 2026-09-20. Do not treat 1,009 and 337 as a paired comparison. The two figures come from different tables: the 337 hits were measured on 2026-09-20 on PostgreSQL 17.11 in container dbg-pgskip17 against the four-value table, whose index is the same 338 pages but whose data is not the data in the plateau rows. The safe conclusion is that the work increases by a small multiple rather than a rounding error.

There is no threshold to memorize here. The cost rises smoothly with the number of groups, and when it stops being worthwhile depends on how selective the later predicate is, how wide the table is, what else is cached and what the cost settings say.

Same table, same index, same query, and a leading column that is unique. PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20:

DROP TABLE IF EXISTS ss_hi;
CREATE TABLE ss_hi (a int, b int, c int);
INSERT INTO ss_hi SELECT i, i % 1000, i FROM generate_series(1, 400000) i;
CREATE INDEX idx_hi_ab ON ss_hi (a, b);
VACUUM ANALYZE ss_hi;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(*) FROM ss_hi WHERE b = 42;
Finalize Aggregate (actual time=13.195..16.290 rows=1.00 loops=1)
Buffers: shared hit=2163
-> Gather (actual time=13.111..16.282 rows=2.00 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Partial Aggregate (actual time=11.016..11.017 rows=1.00 loops=2)
-> Parallel Seq Scan on ss_hi (actual time=0.016..10.986 rows=200.00 loops=2)
Filter: (b = 42)
Rows Removed by Filter: 199800
Execution Time: 16.316 ms

A parallel sequential scan, reading the whole 2,163-page table and no index pages, on PostgreSQL 18.6 in container dbg-pgskip on 2026-09-20. Five runs on that server on that day ran 15.550 to 17.157 ms, and PostgreSQL 17.11 in container dbg-pgskip17 on 2026-09-20 chose the same plan and ran 17.709 to 18.901 ms. This is not a regression in 18: skip scan was not going to help this query.

What the index would have cost is worth measuring rather than assuming. Forcing it with enable_seqscan = off on that PostgreSQL 18.6 container on 2026-09-20 did not produce 400,000 searches. It produced Index Searches: 1 while reading 1,096 index pages. The costs explain why the planner passed on it: on that container and day, the forced plan was estimated at 7401.41 and the parallel sequential scan it chose instead at 6104.99. With no useful grouping to skip among, skip scan is not applied and the result is the full index scan that 17 would have done.

Skip scan and index-only scan are different things

Section titled “Skip scan and index-only scan are different things”

Skip scan decides how much of the index gets visited. An index-only scan decides whether the heap gets visited. They appear together in the friendly example, but they are separate mechanisms.

On the same four-value table, PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, asking for a column the index does not carry gave a bitmap plan that still used the skip:

Aggregate (actual time=1.519..1.520 rows=1.00 loops=1)
-> Bitmap Heap Scan on ss (actual time=0.086..1.477 rows=400.00 loops=1)
Recheck Cond: (b = 42)
Heap Blocks: exact=400
-> Bitmap Index Scan on idx_ss_ab (actual time=0.045..0.045 rows=400.00 loops=1)
Index Cond: (b = 42)
Index Searches: 6

Same six searches, 400 heap blocks, no index-only scan.

The visibility map is the other half of it. On that same server and day, the identical table loaded and ANALYZEd but never vacuumed reported relallvisible of 0 against 2,163 pages, and the query returned the bitmap plan above. After VACUUM, relallvisible was 2,163 and the plan was the index-only scan with the same Index Searches: 6.

Repeated reads do not set those bits. Vacuum does, and autovacuum counts: a table left alone on that PostgreSQL 18.6 container on 2026-09-20 reached relallvisible of 2,163 with no manual VACUUM ever issued, once autovacuum had been by.

How to read the numbers without fooling yourself

Section titled “How to read the numbers without fooling yourself”

Buffer counts are accesses, not pages. The PostgreSQL 18 documentation says so:

The buffer numbers displayed show the count of the non-distinct buffers hit, read, dirtied, and written for the given node and all of its child nodes.

That is from Using EXPLAIN in the PostgreSQL 18 documentation, read 2026-09-20.

They also change for reasons unrelated to the query. The same statement on the same table on PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, reported 19 buffer hits when run five times inside one session after a warm-up, and 25 when each of five runs opened its own connection. Nothing about the data changed between them. Use buffers to compare plans in one session, and measure the index with pg_relation_size when you want its size.

Timings are useful if you repeat them. Five runs each in fresh connections on 2026-09-20: PostgreSQL 18.6 in container dbg-pgskip ran 0.168 to 0.193 ms and PostgreSQL 17.11 in container dbg-pgskip17 ran 0.856 to 1.136 ms. Those ranges do not overlap, so the difference is real on this machine. A single sub-millisecond run would not have shown that, and pg_stat_statements is the better instrument in production because it aggregates calls rather than sampling one.

Ask the statistics what the column looks like. Against the two tables this page has been measuring, substitute your own:

SELECT tablename, attname, n_distinct
FROM pg_stats
WHERE schemaname = 'public'
AND tablename IN ('ss', 'ss_hi')
ORDER BY tablename, attname;

PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, returned 4 for ss.a, 1000 for ss.b, -1 for ss.c, and -1 for ss_hi.a.

n_distinct is a count when positive and a negative fraction of the table when negative, so -1 means every row is different. ss.a at 4 is the table where skip scan helps; ss_hi.a at -1 is the table where the planner declined.

It is an estimate from a sampled ANALYZE, and it is the count over the whole column, not the count of groups the predicate actually makes relevant. Use it to form an expectation, then read the plan:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ss WHERE b = 42;

On PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, that reported Index Cond: (b = 42) with no condition on a, Index Searches: 6, 25 buffer hits, and an estimate of 398 rows against 400 actual. The plan quoted earlier on this page is the same query on the same table and it estimated 397. That one row of difference illustrates the point above: the estimate comes from a sample, so it moves when ANALYZE runs again.

Compare estimated rows to actual rows first. If they disagree badly, the planner is choosing from a picture that does not match your table, and the cardinality argument is moot until you ANALYZE.

Is skip scan the right answer, or is an index?

Section titled “Is skip scan the right answer, or is an index?”

Often it is not. The same query on the same 400,000 rows, with a dedicated index on the searched column, PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20:

index usedIndex Searchesbuffersindex pages
(a, b), skip scan625338
(b), ordinary scan14338

One descent instead of six. The documentation’s advice is similar:

Multicolumn indexes should be used sparingly.

That sentence is from Multicolumn Indexes in the PostgreSQL 18 documentation, read 2026-09-20.

The decision is whether this access pattern is frequent and latency-sensitive enough to deserve its own index, given that the index adds work to every write. Skip scan makes the answer “sometimes no” instead of “always yes”.

Group count is the usual answer. When it is not, work through these. Each one below was run on PostgreSQL 18.6 in container dbg-pgskip on 2026-09-20, against 400,000 row tables of the shape used on this page.

The statistics are stale. The planner costs the choice from n_distinct, so a column whose group count has grown since the last ANALYZE is costed against a number that is no longer true. A table built with four values in a, VACUUM ANALYZEd, then rewritten to 50,000 values in a without a fresh ANALYZE: pg_stats still read 4, and the plan for WHERE b = 42 was estimated at 30.10 while touching 568 buffers. After ANALYZE, n_distinct read -0.1266725 and the identical plan over the identical 568 buffers was estimated at 6545.41. The plan and the work stayed the same. Only the number the planner was choosing from moved, from 30.10 to 6545.41, and that number is what loses to a sequential scan or reorders a join elsewhere in a larger query.

The query is not selective, meaning it needs most of the pages. State it in pages rather than rows, because pages are what a scan actually costs. On the four-value table, PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, WHERE b < 800 matched 320,000 of 400,000 rows and the planner chose a parallel sequential scan reading all 2,163 of the table’s 2,163 pages, 30.687 ms, while WHERE b = 42 matched 400 rows and took the skip scan at 25 buffer hits, 0.170 ms. A predicate matching most rows must touch nearly every page, so a sequential scan wins. The converse does not: a small row fraction is not evidence of a small page count. Measured on PostgreSQL 18.6 in container dbg-pgseq, 2026-09-20, and written up in Why is Postgres using a sequential scan when I have an index?, a predicate matching 2.40 percent of the rows still read 15,773 of 16,667 heap pages. Read the plan’s own Heap Blocks line before concluding either way.

The types do not match. Skip scan is considered only for a condition the index can evaluate, and a wrapped or coerced column often does not produce one. Two failures look different in the plan. On PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, WHERE b::text = '42' kept the index but demoted the predicate to a Parallel Index Only Scan with Filter: ((b)::text = '42'::text) rather than an Index Cond, Index Searches: 1, 339 buffers, 27.878 ms. On that same container and day, WHERE abs(b) = 42 lost the index entirely, giving a Parallel Seq Scan over all 2,163 pages at 26.843 ms, where the bare column gave 6 searches and 25 buffers at 0.214 ms. The tell is Index Cond versus Filter, not whether the index name appears.

It is not a B-tree. The optimization is specific to B-trees, and the documentation describes it under multicolumn B-trees rather than indexes generally. A multicolumn BRIN index on the same 400,000 rows, PostgreSQL 18.6 in container dbg-pgskip, 2026-09-20, forced with enable_seqscan = off because the planner would not have chosen it, gave Index Searches: 1 and Heap Blocks: lossy totaling the whole table, 2,198 buffers and 26.834 ms. A B-tree on the same two columns of the same table gave Index Searches: 6 and 25 buffers at 0.281 ms. BRIN did not gain skip scan, but it did not fail either. It did what BRIN does: name page ranges and make the heap recheck them.

You are not on 18. Check rather than assume, particularly on a managed service where the major version and the marketing version can differ:

SELECT current_setting('server_version_num')::int >= 180000 AS has_skip_scan,
version();

It changes less than the announcement suggests, but the change is real.

It does not make column order unimportant. The table above measured six descents through the composite index against one through an index ordered for the query, on PostgreSQL 18.6 in container dbg-pgskip on 2026-09-20, and the gap widens as the number of groups rises.

It changes the cost of one specific redundancy. An index on (tenant_id, created_at) can now also serve a query filtered on created_at alone, so a separate index on created_at may no longer earn its write cost. That is an index to consider dropping after measuring both plans.

The order to reason in is simple: put the column the query filters on first, and let skip scan be the reason you do not also need a second index, rather than the reason you stop thinking about order.

The first pattern is the index nobody measured. Asked to speed up a query, a model proposes an index, and the proposal is usually reasonable. It cannot know the cardinality of the columns in it because the cardinality is not in the code. So (tenant_id, status) and (status, tenant_id) look equally defensible in review, but they behave differently.

The second is version drift. Skip scan is a PostgreSQL 18 feature, and much generated advice is calibrated to 14 through 17. That produces the old rule, that an index is useless without its leading column, stated as fact. The rule was never strictly true, and since 18 it is further from true.

The third is confident plan reading. Index Searches is new enough that generated explanations of it are often wrong in the direction shown above: they treat any value above one as proof of a skip.

Look at n_distinct before choosing the leading column. It is one query and settles most of the argument.

Read plans with costs on. Estimated against actual rows is the first thing to check, and COSTS OFF hides the planner’s stated reason.

Re-ANALYZE after any bulk load. The decision is made from statistics, so stale statistics produce a plan that is correct for a table you no longer have.

Do not drop a redundant index on the strength of the feature. Drop it on the strength of two plans you ran, and keep the CREATE INDEX statement so putting it back is one command.

  • What is skip scan in PostgreSQL 18?

    A B-tree scan that repositions among the values of an index column the query does not constrain, instead of scanning the index end to end. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgskip, a 400,000 row table whose leading column held four distinct values ran the query with 6 index searches and 25 buffer hits, where PostgreSQL 17.11 in container dbg-pgskip17 read 337 buffers of the same 338-page index. It is a plan the optimizer may choose, not a property you turn on.

  • Why is the planner not choosing it?

    Because it costed something else lower, and the number of groups it would have to reposition among is the usual reason. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgskip, a unique leading column gave a parallel sequential scan of 15.550 ms to 17.157 ms across five runs, and PostgreSQL 17.11 chose the same plan. Forcing the index on that table did not produce many searches: it produced 1 search reading 1,096 index pages.

  • Does Index Searches greater than one prove skip scan?

    No. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgskip, an ordinary primary key lookup reported Index Searches 1, an IN list of four scattered values reported 4 with 16 buffer hits, and an IN list of four adjacent values reported 1 with 4 hits. None of those is a skip scan. Read it with the index definition and the Index Cond, not on its own.

  • Does skip scan mean column order no longer matters?

    No. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgskip, the same query served by a dedicated index on the searched column took 1 index search and 4 buffers, against 6 searches and 25 buffers through skip scan on the composite index. Skip scan lowers the cost of an index whose order does not match the query; it does not make the order free.

  • Do I still need a separate index on the second column?

    Sometimes not, and that is the practical win: one fewer index to maintain on every write. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgskip, a dedicated index on the searched column was 338 pages, the same size as the composite index it would sit beside. Measure both plans on your data before dropping anything, and keep the CREATE INDEX statement.