MIME-Version: 1.0
From: Michael Christofides <michael@pgmustard.com>
Date: Thu, 6 Nov 2025 19:00:46 +0000
Message-ID: 
 <CAFwT4nD8r2XGGw4yVONoLmnCc93te-UgjCcLbuvSsEPtagdSqg@mail.gmail.com>
Subject: Index Searches higher than expected for skip scan
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b2a1dd0642f1af88"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFwT4nD8r2XGGw4yVONoLmnCc93te-UgjCcLbuvSsEPtagdSqg%40mail.gmail.com>
Precedence: bulk

--000000000000b2a1dd0642f1af88
Content-Type: text/plain; charset="UTF-8"

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

--000000000000b2a1dd0642f1af88
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi folks,</div><div><br></div><div>I&#39;m trying to =
understand the new Index Searches field in Postgres 18 explain analyze outp=
ut. I&#39;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) co=
lumn of the index.</div><div><br></div><div>In reality, instead of 2 Index =
Searches, I got 4 (query plan below). I also experimented with a leading co=
lumn with 5 distinct values (expecting 5 searches), and got 7. I&#39;ve not=
 included that below, for brevity.</div><div><br></div><div>I suspect I&#39=
;m missing something obvious in either my understanding or my setup, but wo=
ndered why this might be happening?</div><div><br></div><div>All the best,<=
br>Michael<br></div><div><br></div><div><br></div>CREATE TABLE example (<br=
>=C2=A0 =C2=A0integer_field bigint NOT NULL,<br>=C2=A0 =C2=A0boolean_field =
bool NOT NULL);<br><br>INSERT INTO example (integer_field, boolean_field)<b=
r>=C2=A0 =C2=A0SELECT random () * 10_000,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 random () &lt; 0.5<br>=C2=A0 =C2=A0FROM =C2=A0 generate_series(1, 10=
0_000);<br>=C2=A0 =C2=A0<br>CREATE INDEX bool_int_idx <br>=C2=A0 =C2=A0ON e=
xample (boolean_field, integer_field);<div><br></div><div>VACUUM ANALYZE ex=
ample;<br><br>EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) <br><div>SELECT=
 boolean_field FROM example WHERE integer_field =3D 5432;</div><div><br></d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 <br>------------------------------------------------------------=
---------------------------------------------------------------------------=
<br>=C2=A0Index Only Scan using bool_int_idx on public.example =C2=A0(cost=
=3D0.29..13.04 rows=3D10 width=3D1) (actual time=3D0.230..0.274 rows=3D5.00=
 loops=3D1)<br>=C2=A0 =C2=A0Output: boolean_field<br>=C2=A0 =C2=A0Index Con=
d: (example.integer_field =3D 5432)<br>=C2=A0 =C2=A0Heap Fetches: 0<br>=C2=
=A0 =C2=A0Index Searches: 4<br>=C2=A0 =C2=A0Buffers: shared hit=3D9<br>=C2=
=A0Planning Time: 0.240 ms<br>=C2=A0Execution Time: 0.323 ms</div></div></d=
iv>

--000000000000b2a1dd0642f1af88--