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 projects (
project_id int PRIMARY KEY
, project_name text
, project_value numeric
, project_parent int
);
INSERT INTO projects VALUES
(1, 'foo' , 1000, NULL)
, (2, 'foo_sub1' , 500, 1)
, (3, 'foo_sub2' , 100, 1)
, (4, 'foo_sub11' , 50, 2)
, (5, 'foo_sub111' , 5, 4)
, (6, 'foo_sub1111', 1, 5)
, (7, 'foo_sub12' , 10, 2)
, (8, 'bar' , 5000, NULL)
, (9, 'bar_sub' , 500, 8); -- fixed project_parent
CREATE TABLE
INSERT 0 9
-- Simplified
WITH RECURSIVE cte AS (
SELECT project_id AS project_parent, project_value
FROM projects
WHERE project_id = 1 -- enter id of the base project here !
UNION ALL
SELECT p.project_id, p.project_value
FROM cte
JOIN projects p USING (project_parent)
)
SELECT sum(project_value) AS total_value
FROM cte;
| total_value |
|---|
| 1666 |
SELECT 1
-- For all projects at once
WITH RECURSIVE cte AS (
SELECT project_id, project_id AS project_parent, project_value
FROM projects
WHERE project_parent IS NULL -- all base projects
UNION ALL
SELECT c.project_id, p.project_id, p.project_value
FROM cte c
JOIN projects p USING (project_parent)
)
SELECT project_id, sum(project_value) AS total_value
FROM cte
GROUP BY 1
ORDER BY 1;
| project_id | total_value |
|---|---|
| 1 | 1666 |
| 8 | 5500 |
SELECT 2