public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michael Christofides <[email protected]>
To: [email protected]
Subject: Index Searches higher than expected for skip scan
Date: Thu, 6 Nov 2025 19:00:46 +0000
Message-ID: <CAFwT4nD8r2XGGw4yVONoLmnCc93te-UgjCcLbuvSsEPtagdSqg@mail.gmail.com> (raw)
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
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Index Searches higher than expected for skip scan
In-Reply-To: <CAFwT4nD8r2XGGw4yVONoLmnCc93te-UgjCcLbuvSsEPtagdSqg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox