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 dogs (
  name text
, breeds text[]
);

INSERT INTO dogs (name, breeds) VALUES
  ('Barkley', '{"abc", "xyz"}')
, ('Ponyo'  , '{"zzz", "xyz"}')
, ('Empto'  , '{}')
, ('Nullo'  , NULL)
;

CREATE TABLE breeds (
  guid text
, breed text
);

INSERT INTO breeds (guid, breed) VALUES
  ('abc', 'Maltipoo')
, ('xyz', 'Jack Russel')
, ('zzz', 'Dalmatian')
 ;

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 3


-- LATERAL subquery
SELECT d.name, b.breeds_text
FROM   dogs d
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT b.breed
                FROM   unnest(breeds) a(guid)
                JOIN   breeds b USING (guid)) AS breeds_text
   ) b ON true;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{"Jack Russel",Dalmatian}

Empto
{}

Nullo
{}



SELECT 4


-- LATERAL subquery preserving order
SELECT d.name, b.breeds_text
FROM   dogs d
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT b.breed
                FROM   unnest(breeds) WITH ORDINALITY AS a(guid, ord)
                JOIN   breeds b USING (guid)
                ORDER  BY a.ord) AS breeds_text
   ) b ON true;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{Dalmatian,"Jack Russel"}

Empto
{}

Nullo
{}



SELECT 4


-- correlated subquery preserving order
SELECT d.name
     ,  ARRAY (
         SELECT b.breed
         FROM   unnest(d.breeds) WITH ORDINALITY AS a(guid, ord)
         JOIN   breeds b USING (guid)
         ORDER  BY a.ord
        ) AS breeds_text
FROM   dogs d;


name
breeds_text


Barkley
{Maltipoo,"Jack Russel"}

Ponyo
{Dalmatian,"Jack Russel"}

Empto
{}

Nullo
{}



SELECT 4

name	breeds_text
Barkley	{Maltipoo,"Jack Russel"}
Ponyo	{"Jack Russel",Dalmatian}
Empto	{}
Nullo	{}