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?.

select version();

version

PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

create  table test101 (  doc_id bigserial, document jsonb);
insert into test101(document) values ('{"user_removed" :false}') returning *;
insert into  test101(document)  select '{"user_removed" :false}'::jsonb from generate_series(1,10000);
insert into  test101(document)  select ('{"user_remove"  :false, "test":' || i ||'}'::text)::jsonb
                                from generate_series(1,400) i;

doc_id
document

1
{"user_removed": false}

10000 rows affected

400 rows affected

CREATE INDEX test101_gin_user_removed_na_b ON test101(document)
    where (document ? 'user_removed') is false; --will not work.
CREATE INDEX test101_gin_user_removed_na ON test101 USING GIN (document jsonb_ops)
    where (document ? 'user_removed') is false; --will not work.
explain (analyze)  select * from test101 where  document ? 'user_removed' is false ;

QUERY PLAN

Seq Scan on test101  (cost=0.00..218.01 rows=10297 width=40) (actual time=2.040..2.128 rows=400 loops=1)

  Filter: ((document ? 'user_removed'::text) IS FALSE)

  Rows Removed by Filter: 10001

Planning Time: 0.417 ms

Execution Time: 2.199 ms