Postgres 13

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 reception_time (
    id INT NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);
INSERT INTO reception_time(id, start_time, end_time)
VALUES 
    (1, '8:00', '8:20'),
    (2, '8:20', '8:40'),
    (3, '8:40', '9:00'),
    (4, '9:00', '9:20');
4 rows affected

WITH RECURSIVE 
reception_time_rn AS (
    SELECT *, row_number() over (order by start_time) as rn FROM reception_time
),
recursive_query AS (
    SELECT 
        id, 
        start_time,
        end_time - INTERVAL '5' MINUTE as end_time,
        rn
    FROM reception_time_rn
    WHERE rn = 1

    UNION ALL
    
    SELECT 
        rt.id,
        recursive_query.end_time,
        rt.end_time - (
            rt.start_time - recursive_query.end_time 
            + 
            INTERVAL '5' MINUTE
        ) as end_time,
        rt.rn
    FROM reception_time_rn AS rt
    INNER JOIN recursive_query
    ON recursive_query.rn + 1 = rt.rn
)

UPDATE reception_time AS rt
SET 
    start_time = recursive_query.start_time,
    end_time = recursive_query.end_time
FROM recursive_query
WHERE recursive_query.id = rt.id;
4 rows affected

SELECT * FROM reception_time


id
start_time
end_time


1
08:00:00
08:15:00

2
08:15:00
08:30:00

3
08:30:00
08:45:00

4
08:45:00
09:00:00

id	start_time	end_time
1	08:00:00	08:15:00
2	08:15:00	08:30:00
3	08:30:00	08:45:00
4	08:45:00	09:00:00