Postgres 16

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 00:17' -- no data for first 17 min
                     , timestamp '2018-05-02'
                     , interval '7 min') + random() * interval '7 min';

CREATE TABLE



INSERT 0 204


-- 1 row for every time slot with data in the table
-- LIMIT 5 only for this demo
SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts) AS events
FROM   event e
GROUP  BY 1
ORDER  BY 1
LIMIT  5;


date_bin
events


2018-05-01 00:15:00
2

2018-05-01 00:30:00
2

2018-05-01 00:45:00
3

2018-05-01 01:00:00
2

2018-05-01 01:15:00
2



SELECT 5


-- 1 row for every time slot since 2018-05-01
SELECT start_time, COALESCE(events, 0) AS events
FROM  (
   SELECT generate_series(timestamp '2018-05-01', max(ts), interval '15 min')
   FROM   event
   ) g(start_time)
LEFT   JOIN (
   SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts)  -- "origin" & "stride" in sync
   FROM   event e
   WHERE  e.ts >= '2018-05-01'  -- filter early (optional)
   GROUP  BY 1
   ) e(start_time, events) USING (start_time)
ORDER  BY 1
LIMIT  5;


start_time
events


2018-05-01 00:00:00
0

2018-05-01 00:15:00
2

2018-05-01 00:30:00
2

2018-05-01 00:45:00
3

2018-05-01 01:00:00
2



SELECT 5

date_bin	events
2018-05-01 00:15:00	2
2018-05-01 00:30:00	2
2018-05-01 00:45:00	3
2018-05-01 01:00:00	2
2018-05-01 01:15:00	2

start_time	events
2018-05-01 00:00:00	0
2018-05-01 00:15:00	2
2018-05-01 00:30:00	2
2018-05-01 00:45:00	3
2018-05-01 01:00:00	2