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 tablename (id INTEGER, name VARCHAR(4), value INTEGER, parent_id INTEGER);
INSERT INTO tablename (id, name, value, parent_id) VALUES
('1', 'root', null, null),
('2', 'A', null, '1'),
('3', 'B', null, '1'),
('4', 'A1', null, '2'),
('5', 'A1.1', '2', '4'),
('6', 'A1.2', '3', '4'),
('7', 'A2', null, '2'),
('8', 'A2.1', '5', '7'),
('9', 'B1', null, '3'),
('10', 'B2', 1, '3'),
('11', 'B1.1', '10', '9');
CREATE TABLE
INSERT 0 11
UPDATE tablename AS t
SET "value" = c.value
FROM (
WITH RECURSIVE cte AS(
SELECT t1.id, t1.name, t1.value, t1.parent_id
FROM tablename t1
WHERE NOT EXISTS (SELECT 1 FROM tablename t2 WHERE t2.parent_id = t1.id)
UNION ALL
SELECT t.id, t.name, c.value, t.parent_id
FROM tablename t INNER JOIN cte c
ON c.parent_id = t.id
)
SELECT id, SUM("value") "value"
FROM cte
GROUP BY id
) c
WHERE c.id = t.id;
UPDATE 11
SELECT * FROM tablename;
| id | name | value | parent_id |
|---|---|---|---|
| 1 | root | 21 | null |
| 2 | A | 10 | 1 |
| 3 | B | 11 | 1 |
| 4 | A1 | 5 | 2 |
| 5 | A1.1 | 2 | 4 |
| 6 | A1.2 | 3 | 4 |
| 7 | A2 | 5 | 2 |
| 8 | A2.1 | 5 | 7 |
| 9 | B1 | 10 | 3 |
| 10 | B2 | 1 | 3 |
| 11 | B1.1 | 10 | 9 |
SELECT 11