Postgres 10

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 org_users (
  id character varying (255),
  settings_id character varying (255) -- fk: settings.id
);

create table settings (
  id character varying (255), -- primary key settings_id
  perdiem_settings character varying (255), -- jsonised fk to perdiems.id
  floor_settings character varying (255) -- jsonised fk to floors.id
);

create table perdiems (
  id integer, -- primary key
  name character varying(255)
);

create table floors (
  id integer, -- primary key
  name character varying (255)
);
insert into perdiems (id, name) values (1, 'perdiem 1');
insert into perdiems (id, name) values (2, 'perdiem 2');
insert into perdiems (id, name) values (3, 'perdiem 3');

insert into floors (id, name) values (1, 'floor 1');
insert into floors (id, name) values (2, 'floor 2');
insert into floors (id, name) values (3, 'floor 3');

insert into settings (id, perdiem_settings, floor_settings) values ('setting1', '{"allowed_per_diem_ids":[1, 2]}', '{"allowed_floor_ids":[1]}');
insert into settings (id, perdiem_settings, floor_settings) values ('setting2', '{"allowed_per_diem_ids":[2, 3]}', '{"allowed_floor_ids":[1, 2]}');
insert into settings (id, perdiem_settings, floor_settings) values ('setting3', '{"allowed_per_diem_ids":[3, 1]}', '{"allowed_floor_ids":[1, 2, 3]}');

insert into org_users (id, settings_id) values ('user1', 'setting1');
insert into org_users (id, settings_id) values ('user2', 'setting2');
insert into org_users (id, settings_id) values ('user3', 'setting3');
insert into org_users (id, settings_id) values ('user4', null);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select id , ARRAY[NULL] as perdiems ,ARRAY[NULL] as floors FROM org_users 
WHERE settings_id IS NULL 
 UNION ALL
(
select ou.id, array_agg( DISTINCT pd.name  ),
              array_agg( DISTINCT f.name   )
from org_users ou   
 join settings s on  ( ou.settings_id = s.id  )
   cross join lateral
    json_array_elements_text(((s.perdiem_settings)::json->'allowed_per_diem_ids')::json)
       as jp(perdiem) join
      perdiems pd    
   on pd.id = jp.perdiem::int OR jp.perdiem::int IS NULL
  cross join lateral
    json_array_elements_text(((s.floor_settings)::json->'allowed_floor_ids')::json)
        as js(floor) join
      floors f
   on f.id = js.floor::int
   GROUP BY ou.id
 ) ORDER BY id;
  


id
perdiems
floors


user1
{"perdiem 1","perdiem 2"}
{"floor 1"}

user2
{"perdiem 2","perdiem 3"}
{"floor 1","floor 2"}

user3
{"perdiem 1","perdiem 3"}
{"floor 1","floor 2","floor 3"}

user4
{NULL}
{NULL}

id	perdiems	floors
user1	{"perdiem 1","perdiem 2"}	{"floor 1"}
user2	{"perdiem 2","perdiem 3"}	{"floor 1","floor 2"}
user3	{"perdiem 1","perdiem 3"}	{"floor 1","floor 2","floor 3"}
user4	{NULL}	{NULL}