public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michael Christofides <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: [email protected]
Subject: Re: Index Searches higher than expected for skip scan
Date: Fri, 7 Nov 2025 11:16:34 +0000
Message-ID: <CAFwT4nCcW5_3=Mvp8u5F4tk8D_QRWrjqsNoQY1BeL0hd1Pnyug@mail.gmail.com> (raw)
In-Reply-To: <CAH2-WznKg6Bg5R8Z+TA=DuE8fekLagnuzJCgVYi=y6jEKsv6sQ@mail.gmail.com>
References: <CAFwT4nD8r2XGGw4yVONoLmnCc93te-UgjCcLbuvSsEPtagdSqg@mail.gmail.com>
<CAH2-Wz=BrVUghRp61nwgL2DZwMQ8EJfFphzC43QPhjm07U-61A@mail.gmail.com>
<CAH2-WznKg6Bg5R8Z+TA=DuE8fekLagnuzJCgVYi=y6jEKsv6sQ@mail.gmail.com>
Thank you for the incredibly helpful (and fast) replies Peter.
> Attached is its output when I run your test query. The issue here is
that skip scan thinks that there are 4 distinct skip array values that
it must use:
1. SK_BT_MINVAL
2. false
3. true
4. SK_ISNULL
This output in particular really helped it make sense to me.
> But if the column *was* nullable, adding IS NOT NULL would cut the
number of index searches by 1.
>
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);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using bool_int_idx on public.example (cost=0.29..8.79
rows=10 width=1) (actual time=0.060..0.077 rows=12.00 loops=1)
Output: boolean_field
Index Cond: ((example.boolean_field = ANY ('{t,f}'::boolean[])) AND
(example.integer_field = 5432))
Heap Fetches: 0
Index Searches: 2
Buffers: shared hit=5
Planning Time: 0.265 ms
Execution Time: 0.115 ms
Thanks again,
Michael
view thread (5+ messages) latest in thread
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: <CAFwT4nCcW5_3=Mvp8u5F4tk8D_QRWrjqsNoQY1BeL0hd1Pnyug@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