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 Activity
(
  activity_id int   NOT NULL,
  name        text  NOT NULL,
  CONSTRAINT PK_Activity PRIMARY KEY (activity_id),
  CONSTRAINT AK_Activity UNIQUE (name)
);

CREATE TABLE Parent_Activity
(
  activity_id         INT  NOT NULL,
  parent_activity_Id  INT  NOT NULL,
  CONSTRAINT FK_parent_activity_of_activity FOREIGN KEY (activity_id) REFERENCES activity (activity_id),
  CONSTRAINT FK_activity_child_of_activity FOREIGN KEY (parent_activity_id) REFERENCES activity (activity_id),
  CONSTRAINT PK_parent_activity PRIMARY KEY (activity_id),
  CONSTRAINT AK_parent_activity UNIQUE (parent_activity_id, activity_id)
)
;

CREATE TABLE Period
(
    activity_id int NOT NULL,
    start timestamp NOT NULL,
    duration interval,
    CONSTRAINT FK_Period_For_Activity FOREIGN KEY (activity_id) REFERENCES activity (activity_id),
    CONSTRAINT PK_Period PRIMARY KEY (activity_id, start)
);

INSERT INTO activity(activity_id, name) 
VALUES
    (1, 'C1'),
    (2, 'C1 P1'),
    (3, 'C1 P1 T1'),
    (4, 'C2'),
    (5, 'C2 P1'),
    (6, 'C2 P1 T1'),
    (7, 'TEST')
;

INSERT INTO parent_activity (activity_id, parent_activity_id)
VALUES
  (2,1),
  (3,2),
  (5,4),
  (6,5)
;

INSERT INTO period(activity_id, start, duration) VALUES
    (1, '2021-07-27 09:00:00', '5 mins'),
    (1, '2021-07-27 10:00:00', '6 mins'),
    (1, '2021-07-26 09:00:00', '15 mins'),
    (1, '2021-07-26 10:00:00', '16 mins'),

(2, '2021-07-27 11:00:00', '7 mins'),
    (2, '2021-07-26 11:00:00', '17 mins'),

(3, '2021-07-27 12:00:00', '8 mins'),
    (3, '2021-07-27 13:00:00', '9 mins'),
    (3, '2021-07-27 14:00:00', '10 mins'),
    (3, '2021-07-26 12:00:00', '18 mins'),

(5, '2021-07-27 15:00:00', '11 mins'),

(6, '2021-07-27 15:00:00', '12 mins'),
    (6, '2021-07-27 16:00:00', '13 mins'),
    (6, '2021-07-27 17:00:00', '14 mins'),
    (6, '2021-07-26 13:00:00', '19 mins'),
    (6, '2021-07-26 14:00:00', '20 mins'),
    
    (7, '2021-07-01 08:00:00', '10 mins')
;

7 rows affected

4 rows affected

17 rows affected

WITH RECURSIVE period_rollup AS
(
  SELECT
    act.activity_id AS ultimate_parent_activity_id
   ,act.activity_id
   ,0 as parent_activity_id
   ,1 AS Level
  FROM
    activity act
  WHERE
    act.activity_id NOT IN
      (
        SELECT
          activity_id
        FROM
          parent_activity
      )
    
    UNION ALL
  
  SELECT
    rollup.ultimate_parent_activity_id
   ,par_act.activity_id
   ,par_act.parent_activity_id
   ,rollup.Level + 1 AS level
  FROM
    period_rollup rollup
  INNER JOIN
    parent_activity par_act
      ON par_act.parent_activity_id = rollup.activity_id
)
,cumulative AS
(
  SELECT
    pr.ultimate_parent_activity_id
   ,pr.activity_id
   ,pr.parent_activity_id
   ,pr.level
   ,start.start_date
   ,SUM(EXTRACT(epoch from per.duration)/60) AS activity_duration
   ,COALESCE(SUM(EXTRACT(epoch from per.duration)/60),0) + COALESCE(SUM(SUM(EXTRACT(epoch from per.duration)/60)) OVER (PARTITION BY pr.ultimate_parent_activity_id, start.Start_Date ORDER BY level ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS cumulative_duration
  FROM
    period_rollup pr
  CROSS JOIN
    generate_series
      (
        (SELECT MIN(start) FROM Period)::date
       ,(SELECT MAX(start) FROM Period)::date
       ,'1 day'::interval
      ) start (start_date)
  LEFT JOIN
    period per
      ON per.activity_id = pr.activity_id
          AND date_trunc('day',per.start) = start.start_date
  GROUP BY
    pr.ultimate_parent_activity_id
   ,pr.activity_id
   ,pr.parent_activity_id
   ,pr.level
   ,start.start_date
)
SELECT
  activity_id
 ,parent_activity_id
 ,start_date
 ,cumulative_duration
FROM
  cumulative
WHERE
  cumulative_duration <> 0

activity_id	parent_activity_id	start_date	cumulative_duration
1	0	2021-07-26 00:00:00+01	66
2	1	2021-07-26 00:00:00+01	35
3	2	2021-07-26 00:00:00+01	18
1	0	2021-07-27 00:00:00+01	45
2	1	2021-07-27 00:00:00+01	34
3	2	2021-07-27 00:00:00+01	27
4	0	2021-07-26 00:00:00+01	39
5	4	2021-07-26 00:00:00+01	39
6	5	2021-07-26 00:00:00+01	39
4	0	2021-07-27 00:00:00+01	50
5	4	2021-07-27 00:00:00+01	50
6	5	2021-07-27 00:00:00+01	39
7	0	2021-07-01 00:00:00+01	10