| Unique (cost=35768521.26..35769314.76 rows=105800 width=17) |
| -> Sort (cost=35768521.26..35768785.76 rows=105800 width=17) |
| Sort Key: (date(tt.dt)) DESC, (CASE WHEN ((count(cl.alarm_before) <> 0) OR (count(sh.alarm_before) <> 0)) THEN true ELSE false END) |
| -> GroupAggregate (cost=35310055.07..35757521.73 rows=105800 width=17) |
| Group Key: tt.dt, cl.id |
| -> Sort (cost=35310055.07..35399178.10 rows=35649213 width=44) |
| Sort Key: tt.dt, cl.id |
| -> Nested Loop Left Join (cost=26.91..27548428.64 rows=35649213 width=44) |
| Join Filter: ((((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone >= sh.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone <= sh.end_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= sh.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= sh.start_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= sh.end_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= sh.end_at))) |
| Filter: (((cl.start_at - cl.alarm_before) > '2025-08-02 10:04:40+08'::timestamp with time zone) OR ((sh.start_at - sh.alarm_before) > '2025-08-02 10:04:40+08'::timestamp with time zone)) |
| -> Nested Loop Left Join (cost=0.02..62683.58 rows=157466 width=36) |
| Join Filter: ((((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone >= cl.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone <= cl.end_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= cl.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= cl.start_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= cl.end_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= cl.end_at))) |
| -> Function Scan on generate_series tt (cost=0.02..10.02 rows=1000 width=8) |
| -> Materialize (cost=0.00..517.38 rows=529 width=36) |
| -> Seq Scan on tbl_calendars cl (cost=0.00..514.74 rows=529 width=36) |
| Filter: (created_by = 1004) |
| -> Materialize (cost=26.89..451.85 rows=1369 width=32) |
| -> Bitmap Heap Scan on tbl_schedules sh (cost=26.89..445.01 rows=1369 width=32) |
| Recheck Cond: (created_by = 1004) |
| -> Bitmap Index Scan on tbl_schedules_created_by_idx (cost=0.00..26.55 rows=1369 width=0) |
| Index Cond: (created_by = 1004) |