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 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