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 t (t_id int PRIMARY KEY, t_start timestamp, t_end timestamp);
INSERT INTO t VALUES 
  (1,  '2009-12-03 14:00', '2009-12-04 09:00')
, (2,  '2009-12-03 15:00', '2009-12-07 08:00')
, (3,  '2013-06-24 07:00', '2013-06-24 11:00')
, (4,  '2013-06-23 13:00', '2013-06-25 11:00')
, (5,  '2013-06-23 13:00', '2013-06-25 11:00')
, (6,  '2009-12-03 14:22', '2009-12-04 09:11')
, (7,  '2009-12-03 14:59', '2009-12-03 15:01')
, (8,  '2009-12-03 14:59', '2009-12-04 15:01')
, (9,  '2009-12-03 08:59', '2009-12-03 09:01')
, (10, '2009-12-03 08:59', '2009-12-04 09:01')
, (11, '2009-12-03 08:55', '2009-12-03 09:03')
, (12, '2009-12-03 08:58', '2009-12-03 09:01')
, (13, '2009-12-03 08:59', '2009-12-03 09:02')
, (14, '2009-12-03 07:59', '2009-12-03 08:02')
;

CREATE TABLE



INSERT 0 14


CREATE OR REPLACE FUNCTION f_worktime_1day(_start timestamp, _end timestamp)
  RETURNS interval
  LANGUAGE sql IMMUTABLE AS
$func$
-- _start & _end within one calendar day! - you might want to check ...
SELECT CASE WHEN extract(ISODOW from _start) < 6 THEN (
   SELECT COALESCE(upper(h) - lower(h), '0')
   FROM  (
      SELECT tsrange '[2000-1-1 08:00, 2000-1-1 15:00)' -- hours hard coded
           * tsrange( '2000-1-1'::date + _start::time
                    , '2000-1-1'::date + _end::time) AS h
      ) sub
   ) ELSE '0' END
$func$;

CREATE FUNCTION


CREATE OR REPLACE FUNCTION f_worktime(_start timestamp
                                    , _end timestamp
                                    , OUT work_time interval)
  LANGUAGE plpgsql IMMUTABLE AS
$func$
BEGIN
   CASE _end::date - _start::date  -- spanning how many days?
   WHEN 0 THEN                     -- all in one calendar day
      work_time := f_worktime_1day(_start, _end);
   WHEN 1 THEN                     -- wrap around midnight once
      work_time := f_worktime_1day(_start, NULL)
                +  f_worktime_1day(_end::date, _end);
   ELSE                            -- multiple days
      work_time := f_worktime_1day(_start, NULL)
                +  f_worktime_1day(_end::date, _end)
                + (SELECT count(*) * interval '7:00'  -- workday hard coded!
                   FROM   generate_series(_start::date + 1
                                        , _end::date   - 1, '1 day') AS t
                   WHERE  extract(ISODOW from t) < 6);
   END CASE;
END
$func$;

CREATE FUNCTION


SELECT t_id, t_start, t_end, f_worktime(t_start, t_end)::text
FROM   t
ORDER  BY 1;


t_id
t_start
t_end
f_worktime


1
2009-12-03 14:00:00
2009-12-04 09:00:00
02:00:00

2
2009-12-03 15:00:00
2009-12-07 08:00:00
07:00:00

3
2013-06-24 07:00:00
2013-06-24 11:00:00
03:00:00

4
2013-06-23 13:00:00
2013-06-25 11:00:00
10:00:00

5
2013-06-23 13:00:00
2013-06-25 11:00:00
10:00:00

6
2009-12-03 14:22:00
2009-12-04 09:11:00
01:49:00

7
2009-12-03 14:59:00
2009-12-03 15:01:00
00:01:00

8
2009-12-03 14:59:00
2009-12-04 15:01:00
07:01:00

9
2009-12-03 08:59:00
2009-12-03 09:01:00
00:02:00

10
2009-12-03 08:59:00
2009-12-04 09:01:00
07:02:00

11
2009-12-03 08:55:00
2009-12-03 09:03:00
00:08:00

12
2009-12-03 08:58:00
2009-12-03 09:01:00
00:03:00

13
2009-12-03 08:59:00
2009-12-03 09:02:00
00:03:00

14
2009-12-03 07:59:00
2009-12-03 08:02:00
00:02:00



SELECT 14

t_id	t_start	t_end	f_worktime
1	2009-12-03 14:00:00	2009-12-04 09:00:00	02:00:00
2	2009-12-03 15:00:00	2009-12-07 08:00:00	07:00:00
3	2013-06-24 07:00:00	2013-06-24 11:00:00	03:00:00
4	2013-06-23 13:00:00	2013-06-25 11:00:00	10:00:00
5	2013-06-23 13:00:00	2013-06-25 11:00:00	10:00:00
6	2009-12-03 14:22:00	2009-12-04 09:11:00	01:49:00
7	2009-12-03 14:59:00	2009-12-03 15:01:00	00:01:00
8	2009-12-03 14:59:00	2009-12-04 15:01:00	07:01:00
9	2009-12-03 08:59:00	2009-12-03 09:01:00	00:02:00
10	2009-12-03 08:59:00	2009-12-04 09:01:00	07:02:00
11	2009-12-03 08:55:00	2009-12-03 09:03:00	00:08:00
12	2009-12-03 08:58:00	2009-12-03 09:01:00	00:03:00
13	2009-12-03 08:59:00	2009-12-03 09:02:00	00:03:00
14	2009-12-03 07:59:00	2009-12-03 08:02:00	00:02:00