Why is Postgres 18 not using skip scan on my composite index?
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, becauseIndex Searchesalone does not tell you what happened.
What was measured, and where
Section titled “What was measured, and where”Every number on this page was measured on two containers. A difference between them reflects the version, not the machine.
| servers | PostgreSQL 18.6 in container dbg-pgskip, PostgreSQL 17.11 in container dbg-pgskip17 |
| images | postgres:18.6 and postgres:17.11, defaults, Docker on an 8 vCPU host |
| table | 400,000 rows, a int, b int, c int, index on (a, b), 2,163 table pages |
| date | 2026-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.
What is the problem?
Section titled “What is the problem?”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”.
What it looks like when it works
Section titled “What it looks like when it works”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 msLeave 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:
| query | Index Searches | buffer hits |
|---|---|---|
WHERE id = 42 | 1 | 4 |
WHERE id IN (42, 4242, 42424, 242424) | 4 | 16 |
WHERE id IN (42, 43, 44, 45) | 1 | 4 |
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:
| predicate | Index Searches | buffers |
|---|---|---|
a = 2 AND c = 77 | 33 | hit=67 read=35 |
c = 77 | 126 | hit=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.
What it costs as that count rises
Section titled “What it costs as that count rises”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 a | Index Searches | buffer hits | execution time, three runs (ms) |
|---|---|---|---|
| 4 | 6 | 25 | 0.190, 0.197, 0.224 |
| 10 | 12 | 43 | 0.191, 0.224, 0.260 |
| 50 | 51 | 160 | 0.291, 0.298, 0.477 |
| 100 | 101 | 310 | 0.427, 0.458, 0.559 |
| 200 | 201 | 610 | 0.680, 0.689, 0.720 |
| 500 | 334 | 1,009 | 1.116, 1.134, 1.486 |
| 1000 | 334 | 1,009 | 1.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.
When the planner declines
Section titled “When the planner declines”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 msA 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: 6Same 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.
How do I confirm it on my table?
Section titled “How do I confirm it on my table?”Ask the statistics what the column looks like. Against the two tables this page has been measuring, substitute your own:
SELECT tablename, attname, n_distinctFROM pg_statsWHERE 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 used | Index Searches | buffers | index pages |
|---|---|---|---|
(a, b), skip scan | 6 | 25 | 338 |
(b), ordinary scan | 1 | 4 | 338 |
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”.
The other reasons it is not used
Section titled “The other reasons it is not used”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();What this changes about index design
Section titled “What this changes about index design”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.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”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.
How do I stop it coming back?
Section titled “How do I stop it coming back?”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.