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 paths (id int primary key, name text);
create table Criteria (key text, value text, path_id int);
insert into paths 
values
(1, 'one'),
(2, 'two'),
(3, 'three'),
(4, 'four'),
(5, 'five');
5 rows affected

insert into criteria (path_id, key, value)
values
(1, 'age', '30'),
(1, 'gender', 'male'),
(2, 'age', '31'),
(2, 'gender', 'male'),
(3, 'age', '30'),
(3, 'gender', 'male'),
(3, 'hobby', 'hitchhiking');
7 rows affected

select p.*, c.*
from paths as p 
  join (
     select path_id, jsonb_object_agg(key, value) as all_criteria
     from criteria
     group by path_id
     having jsonb_object_agg(key, value) @> '{"age": "30", "gender": "male"}'
  )  c on c.path_id = p.id


id
name
path_id
all_criteria


3
three
3
{"age": "30", "hobby": "hitchhiking", "gender": "male"}

1
one
1
{"age": "30", "gender": "male"}

id	name	path_id	all_criteria
3	three	3	{"age": "30", "hobby": "hitchhiking", "gender": "male"}
1	one	1	{"age": "30", "gender": "male"}