Postgres 14

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 14

WITH var AS (SELECT '08:00'::time  AS v_start
                  , '15:00'::time  AS v_end)
SELECT t_id
     ,(COALESCE(h.h, '0')
       - CASE WHEN EXTRACT(ISODOW FROM t_start) < 6
               AND t_start::time > v_start
               AND t_start::time < v_end
         THEN t_start - date_trunc('hour', t_start)
         ELSE '0'::interval END
       + CASE WHEN EXTRACT(ISODOW FROM t_end) < 6
               AND t_end::time > v_start
               AND t_end::time < v_end
         THEN t_end - date_trunc('hour', t_end)
         ELSE '0'::interval END)::text           AS work_interval
FROM   t CROSS JOIN var
LEFT   JOIN (
   SELECT t_id, count(*)::int * interval '1h' AS h
   FROM  (
      SELECT t_id, v_start, v_end
           , generate_series (date_trunc('hour', t_start)
                            , date_trunc('hour', t_end) - interval '1h'
                            , interval '1h') AS h
      FROM   t, var
      ) sub
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= v_start
   AND    h::time <= v_end - interval '1h'
   GROUP  BY 1
   ) h USING (t_id)
ORDER  BY 1;

t_id	work_interval
1	02:00:00
2	07:00:00
3	03:00:00
4	10:00:00
5	10:00:00
6	01:49:00
7	00:01:00
8	07:01:00
9	00:02:00
10	07:02:00
11	00:08:00
12	00:03:00
13	00:03:00
14	00:02:00

SELECT 14