Postgres 16

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 table1 (
    id integer,
    date_strt timestamp,
    date_end timestamp,
    strt_unit integer,
    end_unit integer
);

INSERT INTO table1 (id, date_strt, date_end,strt_unit,end_unit)
VALUES
    (1, '2023-10-27 12:00:00','2023-10-31 12:00:00', 5,72),
    (2, '2023-10-30 12:15:00','2023-11-02 00:00:00', 78,90);
	
	
CREATE TABLE table2 (
    id integer,
    dates timestamp,
    unit integer
);

INSERT INTO table2 (id, dates, unit)
VALUES
    (1, '2023-10-28 00:00:00', 55),
    (1, '2023-10-29 00:00:00', 60),
    (1, '2023-10-30 00:00:00', 65),
    (1, '2023-10-31 00:00:00', 70),
    (2, '2023-10-30 00:00:00', 75),
    (2, '2023-10-31 00:00:00', 80),
    (2, '2023-11-01 00:00:00', 85),
    (2, '2023-11-02 00:00:00', 90);

CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 8


with cte as (
  select id, CASE WHEN start_time=date_strt THEN start_time ELSE date(start_time) END as start_time, 
             lead(date(start_time), 1, date_end) over (partition by id order by start_time) as end_time
  from table1 t1
  cross join generate_series
        ( date_strt, date_end, '1 day'::interval) start_time
)
select *
from cte c


id
start_time
end_time


1
2023-10-27 12:00:00
2023-10-28 00:00:00

1
2023-10-28 00:00:00
2023-10-29 00:00:00

1
2023-10-29 00:00:00
2023-10-30 00:00:00

1
2023-10-30 00:00:00
2023-10-31 00:00:00

1
2023-10-31 00:00:00
2023-10-31 12:00:00

2
2023-10-30 12:15:00
2023-10-31 00:00:00

2
2023-10-31 00:00:00
2023-11-01 00:00:00

2
2023-11-01 00:00:00
2023-11-02 00:00:00



SELECT 8


with cte as (
  select t1.*, CASE WHEN start_time=date_strt THEN start_time ELSE date(start_time) END as start_time,
               lead(date(start_time), 1, date_end) over (partition by id order by start_time) as end_time
  from table1 t1
  cross join generate_series
        ( date_strt, date_end, '1 day'::interval) start_time
),
cte2 as (
  select c.id, c.start_time, c.strt_unit, 
         c.end_time, case when c.end_time = date_end then end_unit else unit end as end_value
  from cte c
  inner join table2 t on c.id = t.id and DATE(t.dates) = DATE(c.end_time)
)
select id, start_time,
       lag(end_value, 1, strt_unit) over (partition by id order by start_time) as start_value,
       end_time, end_value
from cte2


id
start_time
start_value
end_time
end_value


1
2023-10-27 12:00:00
5
2023-10-28 00:00:00
55

1
2023-10-28 00:00:00
55
2023-10-29 00:00:00
60

1
2023-10-29 00:00:00
60
2023-10-30 00:00:00
65

1
2023-10-30 00:00:00
65
2023-10-31 00:00:00
70

1
2023-10-31 00:00:00
70
2023-10-31 12:00:00
72

2
2023-10-30 12:15:00
78
2023-10-31 00:00:00
80

2
2023-10-31 00:00:00
80
2023-11-01 00:00:00
85

2
2023-11-01 00:00:00
85
2023-11-02 00:00:00
90



SELECT 8

id	start_time	end_time
1	2023-10-27 12:00:00	2023-10-28 00:00:00
1	2023-10-28 00:00:00	2023-10-29 00:00:00
1	2023-10-29 00:00:00	2023-10-30 00:00:00
1	2023-10-30 00:00:00	2023-10-31 00:00:00
1	2023-10-31 00:00:00	2023-10-31 12:00:00
2	2023-10-30 12:15:00	2023-10-31 00:00:00
2	2023-10-31 00:00:00	2023-11-01 00:00:00
2	2023-11-01 00:00:00	2023-11-02 00:00:00