Postgres 12

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 users(id, name) AS VALUES (2, 'samplehost');
ALTER TABLE users ADD PRIMARY KEY (id);
CREATE TABLE parties(id, host_id) AS VALUES (1, 2);
ALTER TABLE parties ADD PRIMARY KEY (id), ADD FOREIGN KEY (host_id) REFERENCES users;
CREATE TABLE guests(party_refer, name) AS VALUES (1, 'guest1'), (1, 'guest2');
1 rows affected

1 rows affected

2 rows affected

SELECT parties.*, users.name as host_name, json_agg(row_to_json(guests)) as guests
FROM parties 
INNER JOIN users ON parties.host_id = users.id
INNER JOIN guests ON parties.id = guests.party_refer
GROUP BY parties.id, users.name


id
host_id
host_name
guests


1
2
samplehost
[{"party_refer":1,"name":"guest1"}, {"party_refer":1,"name":"guest2"}]


SELECT
  parties.*,
  users.name as host_name,
  (SELECT json_agg(row_to_json(guests))
    FROM guests
    WHERE guests.party_refer = parties.id) as guests
FROM parties 
INNER JOIN users ON parties.host_id = users.id


id
host_id
host_name
guests


1
2
samplehost
[{"party_refer":1,"name":"guest1"}, {"party_refer":1,"name":"guest2"}]