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 users (id int primary key);
create table events (id int primary key, title varchar(255), content varchar(255), status int, creator int references users);
create table accounts (user_id int references users, name varchar(255), surname varchar(255));
create table events_user (id int primary key, events_id int references events, users_id int references users);

insert into users values (1), (2), (3);
insert into accounts values (1, 'pijcke', 'fabian');
insert into accounts values (2, 'shepard', 'adrian');
insert into accounts values (3, 'freeman', 'gordon');
insert into events values (1, 'event 1', 'bla', 1, 1);
insert into events values (2, 'event 2', 'blabla', 42, 1);
insert into events_user values (1, 1, 1);
insert into events_user values (2, 1, 2);
insert into events_user values (3, 2, 2);
insert into events_user values (4, 2, 3);

select events.id, events.title, creator.creator, members.members
from events
inner join lateral (
  SELECT user_id, json_build_object('id', accounts.user_id, 'name', accounts.name, 'surname', accounts.surname)
  FROM accounts
) creator(user_id, creator) ON creator.user_id = events.creator
natural join lateral (
  SELECT events_id AS id, json_agg(json_build_object('id', accounts.user_id, 'name', accounts.name, 'surname', accounts.surname))
  FROM events_user
  INNER JOIN accounts ON accounts.user_id = events_user.users_id
  GROUP BY events_id
) members(id, members);

3 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

id	title	creator	members
1	event 1	{"id" : 1, "name" : "pijcke", "surname" : "fabian"}	[{"id" : 1, "name" : "pijcke", "surname" : "fabian"}, {"id" : 2, "name" : "shepard", "surname" : "adrian"}]
2	event 2	{"id" : 1, "name" : "pijcke", "surname" : "fabian"}	[{"id" : 2, "name" : "shepard", "surname" : "adrian"}, {"id" : 3, "name" : "freeman", "surname" : "gordon"}]