MIME-Version: 1.0
References: 
 <CAFwT4nD8r2XGGw4yVONoLmnCc93te-UgjCcLbuvSsEPtagdSqg@mail.gmail.com>
 <CAH2-Wz=BrVUghRp61nwgL2DZwMQ8EJfFphzC43QPhjm07U-61A@mail.gmail.com>
 <CAH2-WznKg6Bg5R8Z+TA=DuE8fekLagnuzJCgVYi=y6jEKsv6sQ@mail.gmail.com>
 <CAFwT4nCcW5_3=Mvp8u5F4tk8D_QRWrjqsNoQY1BeL0hd1Pnyug@mail.gmail.com>
In-Reply-To: 
 <CAFwT4nCcW5_3=Mvp8u5F4tk8D_QRWrjqsNoQY1BeL0hd1Pnyug@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Fri, 7 Nov 2025 10:00:49 -0500
Message-ID: 
 <CAH2-Wz=x9RTnDJUeN7yq1TftnH00L5jGtAtnB6L6i87b8Yy1fA@mail.gmail.com>
Subject: Re: Index Searches higher than expected for skip scan
To: Michael Christofides <michael@pgmustard.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-Wz%3Dx9RTnDJUeN7yq1TftnH00L5jGtAtnB6L6i87b8Yy1fA%40mail.gmail.com>
Precedence: bulk

On Fri, Nov 7, 2025 at 6:16=E2=80=AFAM Michael Christofides
<michael@pgmustard.com> wrote:
> Thank you for the incredibly helpful (and fast) replies Peter.

You're welcome.

> Nice idea. Once it sunk in, I realised I could try the explicit "AND bool=
ean_field IN (true, false)" and got it down to 2 index searches:
>
> EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
> SELECT boolean_field FROM example WHERE integer_field =3D 5432 AND boolea=
n_field IN (true, false);

That's using the Postgres 17 work. You could also write the query as
"SELECT boolean_field FROM example WHERE integer_field =3D 5432 AND
boolean_field BETWEEN false AND true" and get 2 index searches. That
variant uses what I've called "range skip scan", which is new in
Postgres 18.

--=20
Peter Geoghegan