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 class
(
  identifier int primary key
);

create table person
(
  identifier int primary key,
  name text not null,
  class_identifier int references class
);

create index on person(class_identifier);

insert into class values (1), (2), (3), (4), (5);
insert into person values (1, 'a', 1), (2, 'b', 1), (3, 'c', 1), (4, 'a', 2), (5, 'a', 3), (6, 'a', 4), (7, 'b', 4), (8, 'a', 5);
5 rows affected

8 rows affected

select     c.identifier, array_agg(p.identifier order by p.name desc) as persons
from       class as c
left join lateral (
             select   p.identifier, p.name
             from     person as p
             where    p.class_identifier = c.identifier
             order by p.name desc
             limit    2
           ) as p
on         true
group by   c.identifier
order by   c.identifier;


identifier
persons


1
{3,2}

2
{4}

3
{5}

4
{7,6}

5
{8}


SELECT c.identifier, p.persons
FROM   class c
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) AS persons
   ) p ON true
ORDER  BY c.identifier;


identifier
persons


1
{3,2}

2
{4}

3
{5}

4
{7,6}

5
{8}


SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
   SELECT array_agg(identifier) AS persons
   FROM  (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) sub
   ) p
ORDER  BY c.identifier;


identifier
persons


1
{3,2}

2
{4}

3
{5}

4
{7,6}

5
{8}