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 entities (
id int,
name text
);
INSERT INTO entities VALUES
(1, 'Foo'),
(2, 'Bar');
CREATE TABLE entities_uploads (
entity_id int,
upload_id int,
cover boolean,
profile boolean
);
INSERT INTO entities_uploads VALUES
(1, 1, true, false),
(1, 3, false, false),
(2, 2, true, true);
CREATE TABLE uploads (
id int,
title text,
versions jsonb
);
INSERT INTO uploads VALUES
(1, 'foobar', '[1, 2, 3]'),
(2, 'baz', '[42]'),
(3, 'foofoo', '[]');
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
SELECT
e.id,
e.name,
jsonb_agg(
jsonb_build_object(
'upload_id', upload_id,
'title', title,
'versions', versions,
'cover', cover,
'profile', profile
)
) AS uploads
FROM
entities e
JOIN entities_uploads eu ON e.id = eu.entity_id
JOIN uploads u ON eu.upload_id = u.id
GROUP BY e.id, e.name
| id | name | uploads |
|---|---|---|
| 2 | Bar | [{"cover": true, "title": "baz", "profile": true, "versions": [42], "upload_id": 2}] |
| 1 | Foo | [{"cover": true, "title": "foobar", "profile": false, "versions": [1, 2, 3], "upload_id": 1}, {"cover": false, "title": "foofoo", "profile": false, "versions": [], "upload_id": 3}] |
SELECT 2