Postgres 9.6

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?.

2 rows affected

5 rows affected

WITH hours AS (
    SELECT 
        oh.id as day_id,
        oh.open_hour + '1970-01-01'::date as open_hour, 
        oh.end_hour + '1970-01-01'::date as end_hour, 
        bh.start_time + '1970-01-01'::date as break_start,
        bh.end_time + '1970-01-01'::date as break_end,
        lead(start_time + '1970-01-01'::date) OVER (PARTITION BY oh.id ORDER BY start_time) as next_start_time
    FROM open_hours oh
    LEFT JOIN break_hours bh
    ON oh.id = bh.start_date
)

SELECT day_id, generate_series(open_hour, break_start, interval '30 minutes')::time as time_slot 
FROM (
    SELECT DISTINCT ON (day_id)
        day_id, open_hour, break_start
    FROM hours
    ORDER BY day_id, break_start
)s

UNION

SELECT 
    day_id, generate_series(break_end, next_start_time, interval '30 minutes')::time
FROM ( 
    SELECT  
        day_id, break_end, next_start_time
    FROM
        hours
    WHERE next_start_time IS NOT NULL
) s

UNION

SELECT day_id, generate_series(break_end, end_hour, interval '30 minutes')::time 
FROM (
    SELECT DISTINCT ON (day_id)
        day_id, break_end, end_hour
    FROM hours
    ORDER BY day_id, break_start DESC
) s

ORDER BY day_id, time_slot

day_id	time_slot
1	08:00:00
1	08:30:00
1	09:00:00
1	09:30:00
1	09:45:00
1	10:15:00
1	10:45:00
1	11:30:00
1	12:00:00
1	12:30:00
1	13:00:00
1	13:30:00
1	14:00:00
1	14:30:00
1	15:00:00
1	15:15:00
1	15:45:00
1	16:15:00
1	16:45:00
1	17:15:00
1	17:45:00
2	10:30:00
2	11:00:00
2	11:30:00
2	12:00:00
2	14:45:00
2	15:15:00
2	15:45:00
2	16:15:00
2	16:45:00
2	17:15:00
2	17:45:00
2	19:30:00
2	20:00:00
2	20:30:00