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
