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

1 rows affected

3 rows affected

WITH hours AS (
    SELECT 
        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 (ORDER BY start_time) as next_start_time
    FROM open_hours oh
    LEFT JOIN break_hours bh
    ON oh.id = bh.start_date
)
SELECT generate_series(open_hour, break_start, interval '30 minutes')::time as time_slot 
FROM (
    SELECT 
        open_hour, break_start
    FROM hours
    ORDER BY break_start
    LIMIT 1
)s

UNION

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

UNION

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

time_slot
08:00:00
08:30:00
09:00:00
09:30:00
09:45:00
10:15:00
10:45:00
11:30:00
12:00:00
12:30:00
13:00:00
13:30:00
14:00:00
14:30:00
15:00:00
15:15:00
15:45:00
16:15:00
16:45:00
17:15:00
17:45:00