Re: Index Searches higher than expected for skip scan

[Michael Christofides]

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

Re: Index Searches higher than expected for skip scan

[Peter Geoghegan]

On Fri, Nov 7, 2025 at 6:16 AM Michael Christofides
 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