Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table t(v bytea primary key);
insert into t(v) values
(E'\x12\x31'),
(E'\x12\x34\x56\x78'),
(E'\x12\x35'),
(E'\x12\x34'),
(E'\x12\x38')
5 rows affected

select * from t order by v

v

\x1231

\x1234

\x12345678

\x1235

\x1238

select *
from t
where v >= E'\x12\x34' and v < E'\x12\x35'

v

\x12345678

\x1234

explain
select *
from t
where v >= E'\x12\x34' and v < E'\x12\x35'

QUERY PLAN

Bitmap Heap Scan on t  (cost=4.22..14.39 rows=7 width=32)

  Recheck Cond: ((v >= '\x1234'::bytea) AND (v < '\x1235'::bytea))

  ->  Bitmap Index Scan on t_pkey  (cost=0.00..4.22 rows=7 width=0)

        Index Cond: ((v >= '\x1234'::bytea) AND (v < '\x1235'::bytea))