explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mKDL

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10,193.04..10,580.54 rows=5,000 width=925) (actual rows= loops=)

  • Group Key: g.series, t.id, ((g_1.series)::date), (COALESCE(one_off.start, holiday.start, weekday.start)), (COALESCE(one_off."end", holiday."end", weekday."end"))
2. 0.000 0.000 ↓ 0.0

Sort (cost=10,193.04..10,205.54 rows=5,000 width=1,712) (actual rows= loops=)

  • Sort Key: g.series, ((g_1.series)::date), (COALESCE(one_off.start, holiday.start, weekday.start)), (COALESCE(one_off."end", holiday."end", weekday."end"))
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=285.33..2,465.85 rows=5,000 width=1,712) (actual rows= loops=)

  • Join Filter: ((s.team_member_id = t.id) AND (g.series >= date_trunc('minute'::text, s.start)) AND (g.series < date_trunc('minute'::text, s."end")))
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=285.19..2,320.19 rows=5,000 width=868) (actual rows= loops=)

  • Hash Cond: (t.id = a.team_member_id)
  • Join Filter: ((g.series >= date_trunc('minute'::text, a.start_time)) AND (g.series < date_trunc('minute'::text, a.end_time)))
5. 0.000 0.000 ↓ 0.0

Merge Join (cost=276.46..386.46 rows=5,000 width=864) (actual rows= loops=)

  • Merge Cond: (((g_1.series)::date) = ((g.series)::date))
6. 0.000 0.000 ↓ 0.0

Sort (cost=216.63..219.13 rows=1,000 width=856) (actual rows= loops=)

  • Sort Key: ((g_1.series)::date)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=147.13..166.80 rows=1,000 width=856) (actual rows= loops=)

  • Hash Cond: ((tms.id = one_off.team_member_schedule_id) AND ((g_1.series)::date = one_off.date))
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=134.88..149.18 rows=1,000 width=24) (actual rows= loops=)

  • Merge Cond: (tms.id = weekday.schedule_id)
  • Join Filter: ((weekday.name)::text = to_char(g_1.series, 'FMDay'::text))
9. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=115.66..125.77 rows=1,000 width=20) (actual rows= loops=)

  • Merge Cond: ((tms.id = holiday.schedule_id) AND ((tmh.name)::text = (holiday.name)::text))
10. 0.000 0.000 ↓ 0.0

Sort (cost=87.79..90.29 rows=1,000 width=434) (actual rows= loops=)

  • Sort Key: tms.id, tmh.name
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.97..37.96 rows=1,000 width=434) (actual rows= loops=)

  • Hash Cond: ((g_1.series)::date = tmh.date)
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.16..29.35 rows=1,000 width=16) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..9.34 rows=1 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on team_members t (cost=0.00..1.16 rows=1 width=4) (actual rows= loops=)

  • Filter: (id = 1)
15. 0.000 0.000 ↓ 0.0

Index Scan using team_member_schedules_team_member_id_key on team_member_schedules tms (cost=0.15..8.17 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (team_member_id = 1)
16. 0.000 0.000 ↓ 0.0

Function Scan on generate_series g_1 (cost=0.01..10.01 rows=1,000 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=1.36..1.36 rows=36 width=422) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on calendar_holidays tmh (cost=0.00..1.36 rows=36 width=422) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=27.87..28.73 rows=345 width=23) (actual rows= loops=)

  • Sort Key: holiday.schedule_id, holiday.name
20. 0.000 0.000 ↓ 0.0

Seq Scan on workdays holiday (cost=0.00..13.32 rows=345 width=23) (actual rows= loops=)

  • Filter: ((category)::text = 'holiday'::text)
21. 0.000 0.000 ↓ 0.0

Sort (cost=19.23..19.63 rows=161 width=23) (actual rows= loops=)

  • Sort Key: weekday.schedule_id
22. 0.000 0.000 ↓ 0.0

Seq Scan on workdays weekday (cost=0.00..13.32 rows=161 width=23) (actual rows= loops=)

  • Filter: ((category)::text = 'weekday'::text)
23. 0.000 0.000 ↓ 0.0

Hash (cost=10.90..10.90 rows=90 width=844) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on one_off_workdays one_off (cost=0.00..10.90 rows=90 width=844) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=59.83..62.33 rows=1,000 width=8) (actual rows= loops=)

  • Sort Key: ((g.series)::date)
26. 0.000 0.000 ↓ 0.0

Function Scan on generate_series g (cost=0.00..10.00 rows=1,000 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=8.50..8.50 rows=18 width=24) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on appointments a (cost=0.00..8.50 rows=18 width=24) (actual rows= loops=)

  • Filter: ((team_member_id = 1) AND ((status)::text = ANY ('{requested,approved,confirmed,checked_in}'::text[])))
29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.14..8.17 rows=1 width=24) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using schedule_events_team_member_id_dd2a31e8 on schedule_events s (cost=0.14..8.16 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (team_member_id = 1)