Postgres 9.6

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 companies(id integer not null primary key, name varchar);
insert into companies 
values(1,'Company 1'),(2,'Company 2'),(3,'Company 3');

create table company_categories(id integer not null primary key, name varchar);
insert into company_categories 
values(1, 'Cat1'),(2, 'Cat2'),(3, 'Cat3'),(4, 'Cat4');

create table company_category_associations(company_id integer, category_id integer);
insert into company_category_associations 
values(1,1),(1,4),(2,2),(2,4),(3,1),(3,2),(3,3);
3 rows affected

4 rows affected

7 rows affected

create type cat as (id integer, name varchar);
create type comp as (id integer, name varchar, categories cat[]);
select to_json(array(
  select (
    c.id,
    c.name,
    array(
      select (cc.id, cc.name)::cat
      from company_categories cc 
      join company_category_associations cca on (cca.category_id=cc.id and cca.company_id=c.id)
    ))::comp
  from companies c
)) as companies


companies


[{"id":1,"name":"Company 1","categories":[{"id":1,"name":"Cat1"},{"id":4,"name":"Cat4"}]},{"id":2,"name":"Company 2","categories":[{"id":2,"name":"Cat2"},{"id":4,"name":"Cat4"}]},{"id":3,"name":"Company 3","categories":[{"id":1,"name":"Cat1"},{"id":2,"name":"Cat2"},{"id":3,"name":"Cat3"}]}]