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 eg_person (id int PRIMARY KEY, name text);
INSERT INTO eg_person (id, name) VALUES
  (1, 'alice')
, (2, 'bob')
, (3, 'charlie');

CREATE TABLE eg_assoc (aid int PRIMARY KEY, actors int[], benefactors int[]);
INSERT INTO eg_assoc (aid, actors, benefactors) VALUES
  (1, '{1}'    , '{2}')
, (2, '{1,2}'  , '{3}')
, (3, '{1}'    , '{2,3}')
, (4, '{4}'    , '{1}')
, (5, '{3,2,1}', '{2,1,3}')   -- insightful test cases !
, (6, '{3,3,3}', '{2,2,1}');

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 6


-- PROPER query, Postgres 9.4+
SELECT aid, actors
     , ARRAY(SELECT name
             FROM   unnest(e.actors) WITH ORDINALITY a(id, i)
             JOIN   eg_person p USING (id)
             ORDER  BY a.i) AS act_names
     , benefactors
     , ARRAY(SELECT name
             FROM   unnest(e.benefactors) WITH ORDINALITY b(id, i)
             JOIN   eg_person USING (id)
             ORDER  BY b.i) AS ben_names
FROM   eg_assoc e;


aid
actors
act_names
benefactors
ben_names


1
{1}
{alice}
{2}
{bob}

2
{1,2}
{alice,bob}
{3}
{charlie}

3
{1}
{alice}
{2,3}
{bob,charlie}

4
{4}
{}
{1}
{alice}

5
{3,2,1}
{charlie,bob,alice}
{2,1,3}
{bob,alice,charlie}

6
{3,3,3}
{charlie,charlie,charlie}
{2,2,1}
{bob,bob,alice}



SELECT 6


-- PROPER query, Postgres 9.3
SELECT aid, actors
     , ARRAY(SELECT name
             FROM   generate_subscripts(e.actors, 1) i
             JOIN   eg_person p ON p.id = e.actors[i]
             ORDER  BY i) AS act_names
     , benefactors
     , ARRAY(SELECT name
             FROM   generate_subscripts(e.benefactors, 1) i
             JOIN   eg_person p ON p.id = e.benefactors[i]
             ORDER  BY i) AS ben_names
FROM   eg_assoc e;


aid
actors
act_names
benefactors
ben_names


1
{1}
{alice}
{2}
{bob}

2
{1,2}
{alice,bob}
{3}
{charlie}

3
{1}
{alice}
{2,3}
{bob,charlie}

4
{4}
{}
{1}
{alice}

5
{3,2,1}
{charlie,bob,alice}
{2,1,3}
{bob,alice,charlie}

6
{3,3,3}
{charlie,charlie,charlie}
{2,2,1}
{bob,bob,alice}



SELECT 6


-- correlated subqueries
-- act_names preserving all entries, but not the order
-- ben_names neither preserving all entries nor their order
SELECT aid
     , actors, ARRAY(SELECT name FROM unnest(e.actors) id JOIN eg_person USING (id)) AS act_names
     , benefactors, ARRAY(SELECT name FROM eg_person WHERE id = ANY(e.benefactors)) AS ben_names
FROM   eg_assoc e;


aid
actors
act_names
benefactors
ben_names


1
{1}
{alice}
{2}
{bob}

2
{1,2}
{alice,bob}
{3}
{charlie}

3
{1}
{alice}
{2,3}
{bob,charlie}

4
{4}
{}
{1}
{alice}

5
{3,2,1}
{alice,bob,charlie}
{2,1,3}
{alice,bob,charlie}

6
{3,3,3}
{charlie,charlie,charlie}
{2,2,1}
{alice,bob}



SELECT 6


-- PROPER LATERAL queries
SELECT e.aid, e.actors, a.act_names, e.benefactors, b.ben_names
FROM   eg_assoc e
, LATERAL (
   SELECT ARRAY( SELECT name
                 FROM   generate_subscripts(e.actors, 1) i
                 JOIN   eg_person p ON p.id = e.actors[i]
                 ORDER  BY i)
   ) a(act_names)
, LATERAL (
   SELECT ARRAY( SELECT name
              FROM   generate_subscripts(e.benefactors, 1) i
              JOIN   eg_person p ON p.id = e.benefactors[i]
              ORDER  BY i)
   ) b(ben_names);


aid
actors
act_names
benefactors
ben_names


1
{1}
{alice}
{2}
{bob}

2
{1,2}
{alice,bob}
{3}
{charlie}

3
{1}
{alice}
{2,3}
{bob,charlie}

4
{4}
{}
{1}
{alice}

5
{3,2,1}
{charlie,bob,alice}
{2,1,3}
{bob,alice,charlie}

6
{3,3,3}
{charlie,charlie,charlie}
{2,2,1}
{bob,bob,alice}



SELECT 6


-- LATERAL queries, syntax variants
SELECT e.aid, e.actors, a.act_names, e.benefactors, b.ben_names
FROM   eg_assoc e
LEFT   JOIN LATERAL (SELECT array_agg(name) FROM eg_person WHERE id = ANY(e.actors)) a(act_names) ON TRUE
LEFT   JOIN LATERAL (SELECT ARRAY(SELECT name FROM unnest(e.benefactors) id JOIN eg_person USING (id))) b(ben_names) ON TRUE;


aid
actors
act_names
benefactors
ben_names


1
{1}
{alice}
{2}
{bob}

2
{1,2}
{alice,bob}
{3}
{charlie}

3
{1}
{alice}
{2,3}
{bob,charlie}

4
{4}
null
{1}
{alice}

5
{3,2,1}
{alice,bob,charlie}
{2,1,3}
{alice,bob,charlie}

6
{3,3,3}
{charlie}
{2,2,1}
{alice,bob,bob}



SELECT 6


-- Neither preserving all entries nor their order, and needlessly expensive on top
SELECT aid, min(actors) AS actors
     , array_agg(distinct a_person.name) AS act_names
     , min(benefactors) AS benefactors
     , array_agg(distinct b_person.name) AS ben_names
FROM   eg_assoc
LEFT   JOIN eg_person a_person on a_person.id = any(eg_assoc.actors)
LEFT   JOIN eg_person b_person on b_person.id = any(eg_assoc.benefactors)
GROUP  BY aid;


aid
actors
act_names
benefactors
ben_names


1
{1}
{alice}
{2}
{bob}

2
{1,2}
{alice,bob}
{3}
{charlie}

3
{1}
{alice}
{2,3}
{bob,charlie}

4
{4}
{NULL}
{1}
{alice}

5
{3,2,1}
{alice,bob,charlie}
{2,1,3}
{alice,bob,charlie}

6
{3,3,3}
{charlie}
{2,2,1}
{alice,bob}



SELECT 6
aid	actors	act_names	benefactors	ben_names
1	{1}	{alice}	{2}	{bob}
2	{1,2}	{alice,bob}	{3}	{charlie}
3	{1}	{alice}	{2,3}	{bob,charlie}
4	{4}	{}	{1}	{alice}
5	{3,2,1}	{charlie,bob,alice}	{2,1,3}	{bob,alice,charlie}
6	{3,3,3}	{charlie,charlie,charlie}	{2,2,1}	{bob,bob,alice}