Postgres 12

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 tag_store
(
  id int, 
  tag_id int, 
  tag_value text
);
insert into tag_store
values
(1, 1, 'cd'),
(1, 2, 'tg'),
(1, 3, 'po'),
(2, 1, 'cd'),
(2, 2, 'tg'),
(3, 1, 'xx'),
(3, 2, 'ol');
7 rows affected

with tags as (
  select id, jsonb_agg(jsonb_build_object('tg_id', tag_id, 'tg_value', tag_value)) all_tags
  from tag_store
  group by id
)  
select *
from tags
where all_tags @> '[{ "tg_id":1, "tg_value": "cd"},{"tg_id":2,"tg_value":"tg"},{ "tg_id":3,"tg_value":"po" }]';


id
all_tags


1
[{"tg_id": 1, "tg_value": "cd"}, {"tg_id": 2, "tg_value": "tg"}, {"tg_id": 3, "tg_value": "po"}]