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
