Postgres 13

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 the_table (name text, notes jsonb);
insert into the_table
values
  ('anna', '{"link_to": ["bob"]}'),
  ('bob', '{"link_to": ["anna", "claudia"]}'),
  ('claudia', '{"link_to": []}');
3 rows affected

select name, 
       notes, 
       notes -> 'link_to' as referrals_to,
       (select jsonb_agg(name)
        from the_table t2
        where t2.name <> t1.name
        and t2.notes -> 'link_to' ? t1.name) as referrals_from
from the_table t1;


name
notes
referrals_to
referrals_from


anna
{"link_to": ["bob"]}
["bob"]
["bob"]

bob
{"link_to": ["anna", "claudia"]}
["anna", "claudia"]
["anna"]

claudia
{"link_to": []}
[]
["bob"]

name	notes	referrals_to	referrals_from
anna	{"link_to": ["bob"]}	["bob"]	["bob"]
bob	{"link_to": ["anna", "claudia"]}	["anna", "claudia"]	["anna"]
claudia	{"link_to": []}	[]	["bob"]