Postgres 15

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

INSERT 0 12

with cte as (
   select t.*, to_timestamp(to_char(to_timestamp(t.time/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris' s,
    to_timestamp(to_char(to_timestamp(t.next_time/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris' e
   from tbl_history t
),
dt_diff as (
   select t.entity_id, t.phase, sum(t.e - t.s) dr, sum(justify_interval(t.e - t.s)) dt 
   from cte t where t.phase = 'Fulfill' group by t.entity_id, t.phase
),
weekends as (
  select t.entity_id, t.phase, sum(case when extract(dow from v) in (0, 6) then 1 else 0 end) wt 
  from cte t cross join generate_series(t.s, t.e, interval '1 day') v  
  where t.phase = 'Fulfill' group by t.entity_id, t.phase
)
select t1.entity_id, t1.dr - make_interval(days => w.wt::int) "Duration (excluding saturday and sunday)", 
   (select jsonb_object_agg(d.key, d.value) from jsonb_each(
      jsonb_build_object('years', extract(years from t1.dt), 
       'months', extract(months from t1.dt), 
       'days', extract(days from t1.dt), 
       'hours', extract(hours from t1.dt), 
       'minutes', extract(minutes from t1.dt), 
       'seconds', extract(seconds from t1.dt))) d where d.value::int > 0) "Duration full"
from dt_diff t1 join weekends w on w.entity_id = t1.entity_id

entity_id	Duration (excluding saturday and sunday)	Duration full
1	186 days 23:30:21	{"days": 22, "hours": 23, "months": 8, "minutes": 30, "seconds": 21.000000}
2	267 days 03:32:41	{"days": 15, "hours": 3, "years": 1, "minutes": 32, "seconds": 41.000000}

SELECT 2