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 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, level) AS (
        SELECT item_id, to_jsonb(items), 1
        FROM items
        WHERE NOT EXISTS (
                SELECT 2
                FROM joins
                WHERE items.item_id = joins.item_id
        )
        UNION ALL
        SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json ),
               level + 1
        FROM t
        JOIN joins AS j
                ON t.item_id = j.child_id
        JOIN items AS parent
                ON j.item_id = parent.item_id
        WHERE level < 7
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;

CREATE TABLE

CREATE TABLE

INSERT 0 6

INSERT 0 5

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 } } } }

SELECT 3