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 (
  rows INTEGER,
  start_date timestamp,
  end_date timestamp
);

INSERT INTO t
  (rows, start_date, end_date)
VALUES
  ('1', '2021-08-01 00:04:00', '2021-08-01 02:54:00'),
  ('2', '2021-08-01 04:52:00', '2021-08-01 05:32:00');

CREATE TABLE



INSERT 0 2


select *
from   t


rows
start_date
end_date


1
2021-08-01 00:04:00
2021-08-01 02:54:00

2
2021-08-01 04:52:00
2021-08-01 05:32:00



SELECT 2


with t2 as (
            select *,'failure' as type 
            from   t

  
union all 

  
            select  rows
                   ,coalesce(lag(end_date) over(order by end_date) + interval '1 minute', date_trunc('hour', start_date)) as start_date
                   ,start_date + interval '-1 minute' as end_date
                   ,'normal'
            from   t

  
union all

  
            select  max(rows) 
                   ,max(end_date) + interval '1 minute'
                   ,date_trunc('day', max(end_date)) + interval '1 day -1 minute'
                   ,'normal'
            from    t

order by rows, start_date
           )

select  row_number() over(order by start_date) as rows
       ,start_date
       ,end_date
       ,type
from    t2 


rows
start_date
end_date
type


1
2021-08-01 00:00:00
2021-08-01 00:03:00
normal

2
2021-08-01 00:04:00
2021-08-01 02:54:00
failure

3
2021-08-01 02:55:00
2021-08-01 04:51:00
normal

4
2021-08-01 04:52:00
2021-08-01 05:32:00
failure

5
2021-08-01 05:33:00
2021-08-01 23:59:00
normal



SELECT 5

rows	start_date	end_date
1	2021-08-01 00:04:00	2021-08-01 02:54:00
2	2021-08-01 04:52:00	2021-08-01 05:32:00

rows	start_date	end_date	type
1	2021-08-01 00:00:00	2021-08-01 00:03:00	normal
2	2021-08-01 00:04:00	2021-08-01 02:54:00	failure
3	2021-08-01 02:55:00	2021-08-01 04:51:00	normal
4	2021-08-01 04:52:00	2021-08-01 05:32:00	failure
5	2021-08-01 05:33:00	2021-08-01 23:59:00	normal