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