Postgres 10

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 event(event_id serial, ts timestamp);
INSERT INTO event (ts)
SELECT generate_series(timestamp '2018-05-01'
                     , timestamp '2018-05-08'
                     , interval '7 min') + random() * interval '7 min';

CREATE TABLE



INSERT 0 1441


-- for the whole table, time interval 17 min (LIMIT 5 for this demo only)
WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time
LIMIT  5;


start_time
events


2018-05-01 00:03:30.345273
3

2018-05-01 00:20:30.345273
3

2018-05-01 00:37:30.345273
2

2018-05-01 00:54:30.345273
2

2018-05-01 01:11:30.345273
2



SELECT 5


-- minimal equivalent
SELECT start_time, count(e.ts) AS events
FROM  (SELECT generate_series(min(ts), max(ts), interval '17 min') FROM event) g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '17 min'
GROUP  BY 1
ORDER  BY 1
LIMIT  5;


start_time
events


2018-05-01 00:03:30.345273
3

2018-05-01 00:20:30.345273
3

2018-05-01 00:37:30.345273
2

2018-05-01 00:54:30.345273
2

2018-05-01 01:11:30.345273
2



SELECT 5


-- every 15 minutes in the past week (demo data is soon outdated vs. now()!)
SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), count(e.ts) AS events
FROM   generate_series(date_trunc('day', localtimestamp - interval '7 days')
                     , localtimestamp
                     , interval '15 min') g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '15 min'
GROUP  BY start_time
ORDER  BY start_time
LIMIT  5;


to_char
events


2023-10-23 00:00
0

2023-10-23 00:15
0

2023-10-23 00:30
0

2023-10-23 00:45
0

2023-10-23 01:00
0



SELECT 5

start_time	events
2018-05-01 00:03:30.345273	3
2018-05-01 00:20:30.345273	3
2018-05-01 00:37:30.345273	2
2018-05-01 00:54:30.345273	2
2018-05-01 01:11:30.345273	2

to_char	events
2023-10-23 00:00	0
2023-10-23 00:15	0
2023-10-23 00:30	0
2023-10-23 00:45	0
2023-10-23 01:00	0