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 access as
select '2021-09-09 20:03:31+00'::timestamp as entrancetime, '2021-09-09 20:05:39+00'::timestamp as exittime union all
select '2021-09-09 20:03:58+00'::timestamp as entrancetime, '2021-09-09 20:05:11+00'::timestamp as exittime union all
select '2021-09-09 20:04:29+00'::timestamp as entrancetime, '2021-09-09 20:05:36+00'::timestamp as exittime union all
select '2021-09-09 20:04:09+00'::timestamp as entrancetime, '2021-09-09 20:04:28+00'::timestamp as exittime union all
select '2021-09-09 20:05:15+00'::timestamp as entrancetime, '2021-09-09 20:05:30+00'::timestamp as exittime
SELECT 5
select max(num_concurrent)
from (select a.*,
(select count(*)
from "public"."access" a2
where a2.exittime >= a.entrancetime and
a2.entrancetime <= a.entrancetime
) as num_concurrent
from "public"."access" a
) a;
| max |
|---|
| 3 |
SELECT 1
select v.t, sum(v.inc) as net_inc,
sum(sum(v.inc)) over (order by v.t) as num_concurrent
from "public"."access" a cross join lateral
(values (a.entrancetime, 1), (a.exittime, -1)
) v(t, inc)
group by v.t
order by num_concurrent desc
limit 1;
| t | net_inc | num_concurrent |
|---|---|---|
| 2021-09-09 20:04:09 | 1 | 3 |
SELECT 1