public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Geoghegan <[email protected]>
To: Michael Christofides <[email protected]>
Cc: [email protected]
Subject: Re: Index Searches higher than expected for skip scan
Date: Fri, 7 Nov 2025 10:00:49 -0500
Message-ID: <CAH2-Wz=x9RTnDJUeN7yq1TftnH00L5jGtAtnB6L6i87b8Yy1fA@mail.gmail.com> (raw)
In-Reply-To: <CAFwT4nCcW5_3=Mvp8u5F4tk8D_QRWrjqsNoQY1BeL0hd1Pnyug@mail.gmail.com>
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>
On Fri, Nov 7, 2025 at 6:16 AM Michael Christofides
<[email protected]> 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 boolean_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 = 5432 AND boolean_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 = 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.
--
Peter Geoghegan
view thread (5+ messages)
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], [email protected]
Subject: Re: Index Searches higher than expected for skip scan
In-Reply-To: <CAH2-Wz=x9RTnDJUeN7yq1TftnH00L5jGtAtnB6L6i87b8Yy1fA@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