| Unique (cost=84209647.43..84209649.93 rows=200 width=16) |
| -> Sort (cost=84209647.43..84209647.93 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=84209635.29..84209639.79 rows=200 width=16) |
| Group Key: tt.dt |
| -> Nested Loop Left Join (cost=10.51..81640763.75 rows=205509723 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=10.51..517234.50 rows=1241742 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=5.46..26765.58 rows=42567 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=5.44..300.92 rows=143 width=37) |
| -> Bitmap Heap Scan on tbl_schedules (cost=5.44..300.20 rows=143 width=37) |
| Recheck Cond: (created_by = 1001) |
| Filter: ((start_at IS NOT NULL) OR (end_at IS NOT NULL)) |
| -> Bitmap Index Scan on tbl_schedules_created_by_idx (cost=0.00..5.40 rows=149 width=0) |
| Index Cond: (created_by = 1001) |
| -> Materialize (cost=5.05..310.16 rows=98 width=24) |
| -> Bitmap Heap Scan on tbl_work_logs w (cost=5.05..309.67 rows=98 width=24) |
| Recheck Cond: (created_by = 1001) |
| 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..5.03 rows=98 width=0) |
| Index Cond: (created_by = 1001) |
| -> Materialize (cost=0.00..525.78 rows=556 width=20) |
| -> Seq Scan on tbl_calendars c (cost=0.00..523.00 rows=556 width=20) |
| Filter: ((start_at IS NOT NULL) AND (end_at IS NOT NULL) AND (created_by = 1001)) |