Postgres 15

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

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE events (type int, value int, event_timestamp timestamp);

CREATE TABLE


INSERT INTO events
VALUES (1,  10, '2023-01-01'::timestamp)
     , (2,  10, '2023-01-03'::timestamp)
     , (2,  10, '2023-01-05'::timestamp)
     , (1,  10, '2023-01-07'::timestamp)
;

INSERT 0 4


SELECT generate_series AS timestamp
     , etypes.type
     , COALESCE(events.value, 0) AS value
     , COALESCE(SUM(events.value) OVER (PARTITION BY etypes.type ORDER BY generate_series), 0) AS cumulative_sum
  FROM generate_series('2023-01-01'::timestamp, '2023-01-07'::timestamp, '1 day') AS generate_series
  CROSS JOIN (SELECT DISTINCT type FROM events) AS etypes
  LEFT JOIN events ON generate_series = events.event_timestamp AND events.type = etypes.type
 ORDER BY generate_series, etypes.type
;


timestamp
type
value
cumulative_sum


2023-01-01 00:00:00
1
10
10

2023-01-01 00:00:00
2
0
0

2023-01-02 00:00:00
1
0
10

2023-01-02 00:00:00
2
0
0

2023-01-03 00:00:00
1
0
10

2023-01-03 00:00:00
2
10
10

2023-01-04 00:00:00
1
0
10

2023-01-04 00:00:00
2
0
10

2023-01-05 00:00:00
1
0
10

2023-01-05 00:00:00
2
10
20

2023-01-06 00:00:00
1
0
10

2023-01-06 00:00:00
2
0
20

2023-01-07 00:00:00
1
10
20

2023-01-07 00:00:00
2
0
20



SELECT 14


SELECT generate_series AS timestamp
     , etypes.type
     , COALESCE(events.value, 0) AS value
     , COALESCE(SUM(events.value) OVER (PARTITION BY etypes.type ORDER BY generate_series), 0) AS cumulative_sum
  FROM (SELECT type
             , MIN(event_timestamp) AS mints
             , MAX(event_timestamp) AS maxts
          FROM events
         GROUP BY type
       ) AS etypes
  JOIN LATERAL generate_series(mints, maxts, '1 day') AS generate_series ON 1 = 1
  LEFT JOIN events ON generate_series = events.event_timestamp AND events.type = etypes.type
 ORDER BY generate_series, etypes.type
;


timestamp
type
value
cumulative_sum


2023-01-01 00:00:00
1
10
10

2023-01-02 00:00:00
1
0
10

2023-01-03 00:00:00
1
0
10

2023-01-03 00:00:00
2
10
10

2023-01-04 00:00:00
1
0
10

2023-01-04 00:00:00
2
0
10

2023-01-05 00:00:00
1
0
10

2023-01-05 00:00:00
2
10
20

2023-01-06 00:00:00
1
0
10

2023-01-07 00:00:00
1
10
20



SELECT 10


SELECT generate_series AS timestamp
     , etypes.type
     , COALESCE(events.value, 0) AS value
     , COALESCE(SUM(events.value) OVER (PARTITION BY etypes.type ORDER BY generate_series), 0) AS cumulative_sum
  FROM (SELECT type
             , MIN(event_timestamp) AS mints
             , MAX(event_timestamp) AS maxts
          FROM events
         GROUP BY type
       ) AS etypes
  JOIN LATERAL (SELECT generate_series(mints, maxts, '1 day') AS generate_series) AS gs ON 1 = 1
  LEFT JOIN events ON generate_series = events.event_timestamp AND events.type = etypes.type
 ORDER BY generate_series, etypes.type
;


timestamp
type
value
cumulative_sum


2023-01-01 00:00:00
1
10
10

2023-01-02 00:00:00
1
0
10

2023-01-03 00:00:00
1
0
10

2023-01-03 00:00:00
2
10
10

2023-01-04 00:00:00
1
0
10

2023-01-04 00:00:00
2
0
10

2023-01-05 00:00:00
1
0
10

2023-01-05 00:00:00
2
10
20

2023-01-06 00:00:00
1
0
10

2023-01-07 00:00:00
1
10
20



SELECT 10

timestamp	type	value	cumulative_sum
2023-01-01 00:00:00	1	10	10
2023-01-01 00:00:00	2	0	0
2023-01-02 00:00:00	1	0	10
2023-01-02 00:00:00	2	0	0
2023-01-03 00:00:00	1	0	10
2023-01-03 00:00:00	2	10	10
2023-01-04 00:00:00	1	0	10
2023-01-04 00:00:00	2	0	10
2023-01-05 00:00:00	1	0	10
2023-01-05 00:00:00	2	10	20
2023-01-06 00:00:00	1	0	10
2023-01-06 00:00:00	2	0	20
2023-01-07 00:00:00	1	10	20
2023-01-07 00:00:00	2	0	20