Postgres 12

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

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