Postgres 15

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 values (_jsonb jsonb);
CREATE INDEX values_id_idx ON values (((_jsonb -> '_id'::text) ->> '$o'::text));
explain
select * from values where (_jsonb->'_id')::jsonb->>'$o' = '507f1f77bcf86cd799439011';

QUERY PLAN

Bitmap Heap Scan on "values"  (cost=4.21..14.39 rows=7 width=32)

  Recheck Cond: (((_jsonb -> '_id'::text) ->> '$o'::text) = '507f1f77bcf86cd799439011'::text)

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

        Index Cond: (((_jsonb -> '_id'::text) ->> '$o'::text) = '507f1f77bcf86cd799439011'::text)