| Unique (cost=669780.26..669782.76 rows=200 width=16) |
| -> Sort (cost=669780.26..669780.76 rows=200 width=16) |
| Sort Key: (date(tt.dt)) DESC, (CASE WHEN (count(c.id) <> 0) THEN true ELSE false END), (CASE WHEN (count(tbl_schedules.id) <> 0) THEN true ELSE false END), (CASE WHEN (count(w.id) <> 0) THEN true ELSE false END) |
| -> HashAggregate (cost=669768.12..669772.62 rows=200 width=16) |
| Group Key: tt.dt |
| -> Nested Loop Left Join (cost=9.55..649579.16 rows=1615117 width=20) |
| Join Filter: ((((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone >= c.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone <= c.end_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= c.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= c.start_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= c.end_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= c.end_at))) |
| -> Nested Loop Left Join (cost=9.55..11521.24 rows=27683 width=16) |
| Join Filter: ((((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone >= w.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone <= w.end_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= w.start_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= w.start_at)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= w.end_at) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= w.end_at))) |
| -> Nested Loop Left Join (cost=4.54..298.18 rows=1000 width=12) |
| Join Filter: ((((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone >= CASE WHEN (tbl_schedules.start_at IS NULL) THEN CASE WHEN (tbl_schedules.end_at < now()) THEN CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.created_at ELSE tbl_schedules.end_at END ELSE CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.created_at ELSE now() END END ELSE tbl_schedules.start_at END) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone <= CASE WHEN (tbl_schedules.end_at IS NULL) THEN CASE WHEN (tbl_schedules.start_at > now()) THEN tbl_schedules.start_at ELSE CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.complete_at ELSE now() END END ELSE CASE WHEN ((tbl_schedules.start_at IS NULL) AND (tbl_schedules.end_at < now())) THEN CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.complete_at ELSE now() END ELSE tbl_schedules.end_at END END)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= CASE WHEN (tbl_schedules.start_at IS NULL) THEN CASE WHEN (tbl_schedules.end_at < now()) THEN CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.created_at ELSE tbl_schedules.end_at END ELSE CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.created_at ELSE now() END END ELSE tbl_schedules.start_at END) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= CASE WHEN (tbl_schedules.start_at IS NULL) THEN CASE WHEN (tbl_schedules.end_at < now()) THEN CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.created_at ELSE tbl_schedules.end_at END ELSE CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.created_at ELSE now() END END ELSE tbl_schedules.start_at END)) OR (((((date(tt.dt))::text || ' 00:00:00'::text))::timestamp without time zone <= CASE WHEN (tbl_schedules.end_at IS NULL) THEN CASE WHEN (tbl_schedules.start_at > now()) THEN tbl_schedules.start_at ELSE CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.complete_at ELSE now() END END ELSE CASE WHEN ((tbl_schedules.start_at IS NULL) AND (tbl_schedules.end_at < now())) THEN CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.complete_at ELSE now() END ELSE tbl_schedules.end_at END END) AND ((((date(tt.dt))::text || ' 23:59:59'::text))::timestamp without time zone >= CASE WHEN (tbl_schedules.end_at IS NULL) THEN CASE WHEN (tbl_schedules.start_at > now()) THEN tbl_schedules.start_at ELSE CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.complete_at ELSE now() END END ELSE CASE WHEN ((tbl_schedules.start_at IS NULL) AND (tbl_schedules.end_at < now())) THEN CASE WHEN tbl_schedules.is_complete THEN tbl_schedules.complete_at ELSE now() END ELSE tbl_schedules.end_at END END))) |
| -> Function Scan on generate_series tt (cost=0.02..10.02 rows=1000 width=8) |
| -> Materialize (cost=4.53..103.17 rows=1 width=37) |
| -> Result (cost=4.53..103.16 rows=1 width=37) |
| One-Time Filter: false |
| -> Bitmap Heap Scan on tbl_schedules (cost=4.53..103.16 rows=1 width=37) |
| Recheck Cond: (created_by = 1000) |
| -> Bitmap Index Scan on tbl_schedules_created_by_idx (cost=0.00..4.53 rows=32 width=0) |
| Index Cond: (created_by = 1000) |
| -> Materialize (cost=5.01..295.79 rows=93 width=24) |
| -> Bitmap Heap Scan on tbl_work_logs w (cost=5.01..295.32 rows=93 width=24) |
| Recheck Cond: (created_by = 1000) |
| Filter: ((start_at IS NOT NULL) AND (end_at IS NOT NULL) AND (NOT is_draft)) |
| -> Bitmap Index Scan on tbl_work_logs_created_by_idx (cost=0.00..4.99 rows=93 width=0) |
| Index Cond: (created_by = 1000) |
| -> Materialize (cost=0.00..518.92 rows=196 width=20) |
| -> Seq Scan on tbl_calendars c (cost=0.00..517.94 rows=196 width=20) |
| Filter: ((start_at IS NOT NULL) AND (end_at IS NOT NULL) AND (created_by = 1000)) |