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')
;

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');

6 rows affected

4 rows affected

16 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
)
SELECT
  pr.ultimate_parent_activity_id
 ,pr.activity_id
 ,pr.parent_activity_id
 ,pr.level
 ,CAST(per.start AS DATE) AS start_date
 ,SUM(EXTRACT(epoch from per.duration)/60) AS activity_duration
 ,SUM(EXTRACT(epoch from per.duration)/60) + COALESCE(SUM(SUM(EXTRACT(epoch from per.duration)/60)) OVER (PARTITION BY pr.ultimate_parent_activity_id, CAST(per.start AS DATE) ORDER BY level ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS cumulative_duration
FROM
  period_rollup pr
LEFT JOIN
  period per
    ON per.activity_id = pr.activity_id
GROUP BY
  pr.ultimate_parent_activity_id
 ,pr.activity_id
 ,pr.parent_activity_id
 ,pr.level
 ,CAST(per.start AS DATE)

ultimate_parent_activity_id	activity_id	parent_activity_id	level	start_date	activity_duration	cumulative_duration
1	1	0	1	2021-07-26	31	66
1	2	1	2	2021-07-26	17	35
1	3	2	3	2021-07-26	18	18
1	1	0	1	2021-07-27	11	45
1	2	1	2	2021-07-27	7	34
1	3	2	3	2021-07-27	27	27
4	6	5	3	2021-07-26	39	39
4	5	4	2	2021-07-27	11	50
4	6	5	3	2021-07-27	39	39
4	4	0	1	null	null	null