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 items (
item_id serial PRIMARY KEY,
title text
);
CREATE TABLE joins (
id serial PRIMARY KEY,
item_id int,
child_id int
);
INSERT INTO items (item_id,title) VALUES
(1,'PARENT'),
(2,'LEVEL 2'),
(3,'LEVEL 3.1'),
(4,'LEVEL 4.1'),
(5,'LEVEL 4.2'),
(6,'LEVEL 3.2');
INSERT INTO joins (item_id, child_id) VALUES
(1,2),
(2,3),
(3,4),
(3,5),
(2,6);
WITH RECURSIVE t(item_id, json) AS (
SELECT item_id, to_jsonb(items)
FROM items
WHERE NOT EXISTS (
SELECT 1
FROM joins
WHERE items.item_id = joins.item_id
)
UNION ALL
SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json )
FROM t
JOIN joins AS j
ON t.item_id = j.child_id
6 rows affected
5 rows affected
| item_id | jsonb_pretty |
|---|---|
| 1 | { "title": "PARENT", "item_id": 1, "children": { "title": "LEVEL 2", "item_id": 2, "children": { "title": "LEVEL 3.2", "item_id": 6 } } } |
| 1 | { "title": "PARENT", "item_id": 1, "children": { "title": "LEVEL 2", "item_id": 2, "children": { "title": "LEVEL 3.1", "item_id": 3, "children": { "title": "LEVEL 4.1", "item_id": 4 } } } } |
| 1 | { "title": "PARENT", "item_id": 1, "children": { "title": "LEVEL 2", "item_id": 2, "children": { "title": "LEVEL 3.1", "item_id": 3, "children": { "title": "LEVEL 4.2", "item_id": 5 } } } } |