Postgres 15

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 b (id int, table_a int);
INSERT INTO b VALUES
  (100, 1)
, (101, 1)
, (102, 2)
, (103, 2)
;

CREATE TABLE c (id int, table_b int, item text);
INSERT INTO c VALUES
  (200, 100, 'Apple')
, (201, 100, 'Apple')
, (202, 101, 'Orange')
, (203, 102, 'Apple')
, (204, 103, 'Banana')
, (205, 103, 'Rice')
;

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 6


SELECT id_table_a
     , string_agg(c.item, ', ' ORDER BY c_id) AS items
FROM  (
   SELECT b.table_a AS id_table_a, c.item, min(c.id) AS c_id
   FROM   b
   JOIN   c ON c.table_b = b.id
   GROUP  BY b.table_a, c.item
   ) c
GROUP  BY id_table_a
ORDER  BY id_table_a;


id_table_a
items


1
Apple, Orange

2
Apple, Banana, Rice



SELECT 2


-- typically faster to sort in subquery once:
SELECT id_table_a
     , string_agg(c.item, ', ') AS items
FROM  (
   SELECT b.table_a AS id_table_a, c.item
   FROM   b
   JOIN   c ON c.table_b = b.id
   GROUP  BY b.table_a, c.item
   ORDER  BY b.table_a, min(c.id)
   ) c
GROUP  BY id_table_a
ORDER  BY id_table_a;


id_table_a
items


1
Apple, Orange

2
Apple, Banana, Rice



SELECT 2