| 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
| 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 |