Postgres 17

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 mytable (user_id int, datetime timestamp, direction varchar(3));

CREATE TABLE


insert into mytable (user_id, datetime, direction) values
(1, timestamp '2025-02-17 18:25:02', 'in'),
(1, timestamp '2025-02-17 20:09:10', 'out'),
(2, timestamp '2025-02-17 09:55:57', 'in'),
(2, timestamp '2025-02-17 20:48:37', 'out');

INSERT 0 4


with
  hours (hour) as
  (
    select * 
    from generate_series( (select date_trunc('hour', min(datetime)) from mytable),
                          (select date_trunc('hour', max(datetime)) from mytable),
                          interval '1 hour') gen
  ),
  cumulated as
  (
    select
      h.hour,
      sum(case t.direction when 'in' then 1 when 'out' then -1 end) over (order by h.hour, t.datetime) as users
    from hours h
    left join mytable t on t.datetime >= h.hour
                       and t.datetime < h.hour + interval '1 hour'
    
  )
select hour, max(users)
from cumulated
group by hour
order by hour;


hour
max


2025-02-17 09:00:00
1

2025-02-17 10:00:00
1

2025-02-17 11:00:00
1

2025-02-17 12:00:00
1

2025-02-17 13:00:00
1

2025-02-17 14:00:00
1

2025-02-17 15:00:00
1

2025-02-17 16:00:00
1

2025-02-17 17:00:00
1

2025-02-17 18:00:00
2

2025-02-17 19:00:00
2

2025-02-17 20:00:00
1



SELECT 12

hour	max
2025-02-17 09:00:00	1
2025-02-17 10:00:00	1
2025-02-17 11:00:00	1
2025-02-17 12:00:00	1
2025-02-17 13:00:00	1
2025-02-17 14:00:00	1
2025-02-17 15:00:00	1
2025-02-17 16:00:00	1
2025-02-17 17:00:00	1
2025-02-17 18:00:00	2
2025-02-17 19:00:00	2
2025-02-17 20:00:00	1