Index Searches higher than expected for skip scan

From: Michael Christofides <michael(at)pgmustard(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Index Searches higher than expected for skip scan
Date: 2025-11-06 19:00:46
Message-ID: CAFwT4nD8r2XGGw4yVONoLmnCc93te-UgjCcLbuvSsEPtagdSqg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi folks,

I'm trying to understand the new Index Searches field in Postgres 18
explain analyze output. I've put together a super simple test case (below)
expecting a skip scan with 2 Index Searches, one for each value in the
leading (boolean) column of the index.

In reality, instead of 2 Index Searches, I got 4 (query plan below). I also
experimented with a leading column with 5 distinct values (expecting 5
searches), and got 7. I've not included that below, for brevity.

I suspect I'm missing something obvious in either my understanding or my
setup, but wondered why this might be happening?

All the best,
Michael

CREATE TABLE example (
integer_field bigint NOT NULL,
boolean_field bool NOT NULL);

INSERT INTO example (integer_field, boolean_field)
SELECT random () * 10_000,
random () < 0.5
FROM generate_series(1, 100_000);

CREATE INDEX bool_int_idx
ON example (boolean_field, integer_field);

VACUUM ANALYZE example;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT boolean_field FROM example WHERE integer_field = 5432;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using bool_int_idx on public.example (cost=0.29..13.04
rows=10 width=1) (actual time=0.230..0.274 rows=5.00 loops=1)
Output: boolean_field
Index Cond: (example.integer_field = 5432)
Heap Fetches: 0
Index Searches: 4
Buffers: shared hit=9
Planning Time: 0.240 ms
Execution Time: 0.323 ms

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Peter Geoghegan 2025-11-06 19:54:40 Re: Index Searches higher than expected for skip scan
Previous Message Dirschel, Steve 2025-11-05 20:18:18 RE: [EXT] Re: Problem getting query to use index inside a function