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 sessions as
    select 'USER1' as user_id, '2021-02-03 09:23:00'::timestamp as login_ts, '2021-02-03 11:44:00'::timestamp as logout_ts union all
    select 'USER2' as user_id, '2021-02-03 10:49:00'::timestamp as login_ts, '2021-02-03 13:30:00'::timestamp as logout_ts union all
    select 'USER3' as user_id, '2021-02-03 13:32:00'::timestamp as login_ts, '2021-02-03 15:31:00'::timestamp as logout_ts union all
    select 'USER4' as user_id, '2021-02-04 13:50:00'::timestamp as login_ts, '2021-04-03 14:53:00'::timestamp as logout_ts union all
    select 'USER5' as user_id, '2021-02-04 14:44:00'::timestamp as login_ts, '2021-04-03 15:21:00'::timestamp as logout_ts union all
    select 'USER6' as user_id, '2021-02-04 14:52:00'::timestamp as login_ts, '2021-04-03 17:59:00'::timestamp as logout_ts 
6 rows affected

select *
from sessions


user_id
login_ts
logout_ts


USER1
2021-02-03 09:23:00
2021-02-03 11:44:00

USER2
2021-02-03 10:49:00
2021-02-03 13:30:00

USER3
2021-02-03 13:32:00
2021-02-03 15:31:00

USER4
2021-02-04 13:50:00
2021-04-03 14:53:00

USER5
2021-02-04 14:44:00
2021-04-03 15:21:00

USER6
2021-02-04 14:52:00
2021-04-03 17:59:00


with overlap as (
      select v.ts, sum(v.inc) as inc,
             sum(sum(v.inc)) over (order by v.ts) as num_overlaps,
             lead(v.ts) over (order by v.ts) as next_ts
      from sessions s cross join lateral
           (values (login_ts, 1), (logout_ts, -1)) v(ts, inc)
      group by v.ts
     )
select *
from overlap
order by ts


ts
inc
num_overlaps
next_ts


2021-02-03 09:23:00
1
1
2021-02-03 10:49:00

2021-02-03 10:49:00
1
2
2021-02-03 11:44:00

2021-02-03 11:44:00
-1
1
2021-02-03 13:30:00

2021-02-03 13:30:00
-1
0
2021-02-03 13:32:00

2021-02-03 13:32:00
1
1
2021-02-03 15:31:00

2021-02-03 15:31:00
-1
0
2021-02-04 13:50:00

2021-02-04 13:50:00
1
1
2021-02-04 14:44:00

2021-02-04 14:44:00
1
2
2021-02-04 14:52:00

2021-02-04 14:52:00
1
3
2021-04-03 14:53:00

2021-04-03 14:53:00
-1
2
2021-04-03 15:21:00

2021-04-03 15:21:00
-1
1
2021-04-03 17:59:00

2021-04-03 17:59:00
-1
0
null


with overlap as (
      select v.ts, sum(v.inc) as inc,
             sum(sum(v.inc)) over (order by v.ts) as num_overlaps,
             lead(v.ts) over (order by v.ts) as next_ts
      from sessions s cross join lateral
           (values (login_ts, 1), (logout_ts, -1)) v(ts, inc)
      group by v.ts
     )
select gs.hh, coalesce(max(o.num_overlaps), 0) as num_overlaps
from generate_series('2021-02-03'::date, '2021-02-05'::date, interval '1 hour') gs(hh) left join
     overlap o
     on o.ts < gs.hh + interval '1 hour' and
        o.next_ts > gs.hh
group by gs.hh
order by gs.hh


hh
num_overlaps


2021-02-03 00:00:00+00
0

2021-02-03 01:00:00+00
0

2021-02-03 02:00:00+00
0

2021-02-03 03:00:00+00
0

2021-02-03 04:00:00+00
0

2021-02-03 05:00:00+00
0

2021-02-03 06:00:00+00
0

2021-02-03 07:00:00+00
0

2021-02-03 08:00:00+00
0

2021-02-03 09:00:00+00
1

2021-02-03 10:00:00+00
2

2021-02-03 11:00:00+00
2

2021-02-03 12:00:00+00
1

2021-02-03 13:00:00+00
1

2021-02-03 14:00:00+00
1

2021-02-03 15:00:00+00
1

2021-02-03 16:00:00+00
0

2021-02-03 17:00:00+00
0

2021-02-03 18:00:00+00
0

2021-02-03 19:00:00+00
0

2021-02-03 20:00:00+00
0

2021-02-03 21:00:00+00
0

2021-02-03 22:00:00+00
0

2021-02-03 23:00:00+00
0

2021-02-04 00:00:00+00
0

2021-02-04 01:00:00+00
0

2021-02-04 02:00:00+00
0

2021-02-04 03:00:00+00
0

2021-02-04 04:00:00+00
0

2021-02-04 05:00:00+00
0

2021-02-04 06:00:00+00
0

2021-02-04 07:00:00+00
0

2021-02-04 08:00:00+00
0

2021-02-04 09:00:00+00
0

2021-02-04 10:00:00+00
0

2021-02-04 11:00:00+00
0

2021-02-04 12:00:00+00
0

2021-02-04 13:00:00+00
1

2021-02-04 14:00:00+00
3

2021-02-04 15:00:00+00
3

2021-02-04 16:00:00+00
3

2021-02-04 17:00:00+00
3

2021-02-04 18:00:00+00
3

2021-02-04 19:00:00+00
3

2021-02-04 20:00:00+00
3

2021-02-04 21:00:00+00
3

2021-02-04 22:00:00+00
3

2021-02-04 23:00:00+00
3

2021-02-05 00:00:00+00
3

user_id	login_ts	logout_ts
USER1	2021-02-03 09:23:00	2021-02-03 11:44:00
USER2	2021-02-03 10:49:00	2021-02-03 13:30:00
USER3	2021-02-03 13:32:00	2021-02-03 15:31:00
USER4	2021-02-04 13:50:00	2021-04-03 14:53:00
USER5	2021-02-04 14:44:00	2021-04-03 15:21:00
USER6	2021-02-04 14:52:00	2021-04-03 17:59:00

hh	num_overlaps
2021-02-03 00:00:00+00	0
2021-02-03 01:00:00+00	0
2021-02-03 02:00:00+00	0
2021-02-03 03:00:00+00	0
2021-02-03 04:00:00+00	0
2021-02-03 05:00:00+00	0
2021-02-03 06:00:00+00	0
2021-02-03 07:00:00+00	0
2021-02-03 08:00:00+00	0
2021-02-03 09:00:00+00	1
2021-02-03 10:00:00+00	2
2021-02-03 11:00:00+00	2
2021-02-03 12:00:00+00	1
2021-02-03 13:00:00+00	1
2021-02-03 14:00:00+00	1
2021-02-03 15:00:00+00	1
2021-02-03 16:00:00+00	0
2021-02-03 17:00:00+00	0
2021-02-03 18:00:00+00	0
2021-02-03 19:00:00+00	0
2021-02-03 20:00:00+00	0
2021-02-03 21:00:00+00	0
2021-02-03 22:00:00+00	0
2021-02-03 23:00:00+00	0
2021-02-04 00:00:00+00	0
2021-02-04 01:00:00+00	0
2021-02-04 02:00:00+00	0
2021-02-04 03:00:00+00	0
2021-02-04 04:00:00+00	0
2021-02-04 05:00:00+00	0
2021-02-04 06:00:00+00	0
2021-02-04 07:00:00+00	0
2021-02-04 08:00:00+00	0
2021-02-04 09:00:00+00	0
2021-02-04 10:00:00+00	0
2021-02-04 11:00:00+00	0
2021-02-04 12:00:00+00	0
2021-02-04 13:00:00+00	1
2021-02-04 14:00:00+00	3
2021-02-04 15:00:00+00	3
2021-02-04 16:00:00+00	3
2021-02-04 17:00:00+00	3
2021-02-04 18:00:00+00	3
2021-02-04 19:00:00+00	3
2021-02-04 20:00:00+00	3
2021-02-04 21:00:00+00	3
2021-02-04 22:00:00+00	3
2021-02-04 23:00:00+00	3
2021-02-05 00:00:00+00	3