explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9NrW

Settings
# exclusive inclusive rows x rows loops node
1. 0.889 139,635.688 ↑ 24.8 484 1

Sort (cost=1,934.19..1,964.19 rows=12,000 width=252) (actual time=139,635.486..139,635.688 rows=484 loops=1)

  • Sort Key: sw.week, r.roomsize DESC
  • Sort Method: quicksort Memory: 93kB
2.          

CTE semesterhours

3. 7.229 7.245 ↓ 3.6 3,553 1

ProjectSet (cost=0..6.31 rows=1,000 width=44) (actual time=0.013..7.245 rows=3,553 loops=1)

4. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on semester s (cost=0..1.29 rows=1 width=12) (actual time=0.007..0.016 rows=1 loops=1)

  • Filter: ((id)::text = '19h'::text)
5.          

CTE semesterweeks

6. 0.044 0.074 ↑ 45.5 22 1

ProjectSet (cost=0..6.31 rows=1,000 width=44) (actual time=0.049..0.074 rows=22 loops=1)

7. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on semester s_1 (cost=0..1.29 rows=1 width=12) (actual time=0.026..0.03 rows=1 loops=1)

  • Filter: ((id)::text = '19h'::text)
8.          

CTE roomselection

9. 0.102 1.592 ↓ 1.8 22 1

Aggregate (cost=90.39..90.93 rows=12 width=89) (actual time=1.495..1.592 rows=22 loops=1)

10.          

Initplan (forAggregate)

11. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on semester semester (cost=0..1.29 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=1)

  • Filter: ((id)::text = '19h'::text)
12. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on semester semester_1 (cost=0..1.29 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((id)::text = '19h'::text)
13. 0.046 1.478 ↓ 1.8 22 1

Sort (cost=87.81..87.84 rows=12 width=56) (actual time=1.466..1.478 rows=22 loops=1)

  • Sort Key: r_2.id, b_1.id
  • Sort Method: quicksort Memory: 26kB
14. 0.032 1.432 ↓ 1.8 22 1

Merge Join (cost=86.55..87.6 rows=12 width=56) (actual time=1.402..1.432 rows=22 loops=1)

15. 0.031 0.031 ↑ 127.0 11 1

Index Scan using room_parent_roomid on room rchildren (cost=0.28..133.69 rows=1,397 width=17) (actual time=0.017..0.031 rows=11 loops=1)

16. 0.063 1.369 ↓ 1.8 22 1

Sort (cost=86.27..86.3 rows=12 width=47) (actual time=1.359..1.369 rows=22 loops=1)

  • Sort Key: r_2.id
  • Sort Method: quicksort Memory: 26kB
17. 0.162 1.306 ↓ 1.8 22 1

Hash Join (cost=10.41..86.05 rows=12 width=47) (actual time=0.234..1.306 rows=22 loops=1)

18. 1.025 1.025 ↓ 3.4 255 1

Seq Scan on room r_2 (cost=0..75.45 rows=74 width=47) (actual time=0.029..1.025 rows=255 loops=1)

  • Filter: ((parent_roomid IS NULL) AND (active_from < $2) AND ((active_to IS NULL) OR (active_to > $3)) AND ('SEM'::text = (typeid)::text))
19. 0.023 0.119 ↑ 1.0 26 1

Hash (cost=10.09..10.09 rows=26 width=4) (actual time=0.118..0.119 rows=26 loops=1)

20. 0.096 0.096 ↑ 1.0 26 1

Seq Scan on building b_1 (cost=0..10.09 rows=26 width=4) (actual time=0.024..0.096 rows=26 loops=1)

  • Filter: ('SE'::text = (areaid)::text)
21. 1.187 139,634.799 ↑ 24.8 484 1

Hash Join (cost=716.95..1,017.6 rows=12,000 width=252) (actual time=139,632.999..139,634.799 rows=484 loops=1)

22. 0.486 2.503 ↑ 24.8 484 1

Nested Loop (cost=0.14..176.22 rows=12,000 width=180) (actual time=1.621..2.503 rows=484 loops=1)

23. 0.103 0.103 ↑ 45.5 22 1

CTE Scan on semesterweeks sw (cost=0..20 rows=1,000 width=8) (actual time=0.052..0.103 rows=22 loops=1)

24. 0.243 1.914 ↓ 1.8 22 22

Materialize (cost=0.14..6.25 rows=12 width=172) (actual time=0.072..0.087 rows=22 loops=22)

25. 0.047 1.671 ↓ 1.8 22 1

Nested Loop (cost=0.14..6.19 rows=12 width=172) (actual time=1.562..1.671 rows=22 loops=1)

26. 1.514 1.514 ↓ 1.8 22 1

CTE Scan on roomselection r (cost=0..0.24 rows=12 width=172) (actual time=1.498..1.514 rows=22 loops=1)

27. 0.110 0.110 ↑ 1.0 1 22

Index Only Scan using building_pkey on building b (cost=0.14..0.5 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=22)

  • Index Cond: (id = (r.buildingid)::text)
28. 0.003 139,631.109 ↓ 0.0 0 1

Hash (cost=716.79..716.79 rows=1 width=138) (actual time=139,631.109..139,631.109 rows=0 loops=1)

29. 0.001 139,631.106 ↓ 0.0 0 1

Subquery Scan on weekusage (cost=716.76..716.79 rows=1 width=138) (actual time=139,631.106..139,631.106 rows=0 loops=1)

30. 0.002 139,631.105 ↓ 0.0 0 1

Aggregate (cost=716.76..716.78 rows=1 width=138) (actual time=139,631.105..139,631.105 rows=0 loops=1)

31. 0.014 139,631.103 ↓ 0.0 0 1

Sort (cost=716.76..716.76 rows=1 width=134) (actual time=139,631.103..139,631.103 rows=0 loops=1)

  • Sort Key: (to_char(hourusage.hour, 'IW'::text)), hourusage.roomid
  • Sort Method: quicksort Memory: 25kB
32. 0.002 139,631.089 ↓ 0.0 0 1

Subquery Scan on hourusage (cost=716.72..716.75 rows=1 width=134) (actual time=139,631.088..139,631.089 rows=0 loops=1)

33. 0.000 139,631.087 ↓ 0.0 0 1

Unique (cost=716.72..716.74 rows=1 width=110) (actual time=139,631.087..139,631.087 rows=0 loops=1)

34. 0.004 139,631.088 ↓ 0.0 0 1

Group (cost=716.72..716.73 rows=1 width=110) (actual time=139,631.086..139,631.088 rows=0 loops=1)

35. 0.008 139,631.084 ↓ 0.0 0 1

Sort (cost=716.72..716.73 rows=1 width=106) (actual time=139,631.084..139,631.084 rows=0 loops=1)

  • Sort Key: sh.hour, r_1.roomid
  • Sort Method: quicksort Memory: 25kB
36. 0.072 139,631.076 ↓ 0.0 0 1

Nested Loop (cost=4.06..716.71 rows=1 width=106) (actual time=139,631.075..139,631.076 rows=0 loops=1)

37. 0.128 0.128 ↓ 1.8 22 1

CTE Scan on roomselection r_1 (cost=0..0.24 rows=12 width=130) (actual time=0.001..0.128 rows=22 loops=1)

38. 0.051 139,630.876 ↑ 1.3 3 22

Materialize (cost=4.06..715.16 rows=4 width=16) (actual time=218.836..6,346.858 rows=3 loops=22)

39. 0.030 139,630.825 ↑ 1.3 3 1

Nested Loop (cost=4.06..715.14 rows=4 width=16) (actual time=4,814.38..139,630.825 rows=3 loops=1)

40. 81,581.596 139,630.588 ↑ 1.3 3 1

Nested Loop (cost=3.63..713.27 rows=4 width=55) (actual time=4,814.26..139,630.588 rows=3 loops=1)

41. 24.592 24.592 ↓ 169.6 848 1

CTE Scan on semesterhours sh (cost=0..30 rows=5 width=8) (actual time=0.04..24.592 rows=848 loops=1)

  • Filter: (((hour)::time without time zone <@ '[08:00:00,16:00:00)'::timerange) AND (date_part('dow'::text, hour) <> ALL ('{0,6}'::double precision[])))
42. 54,779.042 58,024.400 ↓ 857.0 143,117 848

Materialize (cost=3.63..666.98 rows=167 width=69) (actual time=0.001..68.425 rows=143,117 loops=848)

43. 262.695 3,245.358 ↓ 857.0 143,117 1

Nested Loop (cost=3.63..666.15 rows=167 width=69) (actual time=0.13..3,245.358 rows=143,117 loops=1)

44. 135.091 1,180.625 ↓ 748.4 128,717 1

Nested Loop (cost=3.21..559.9 rows=172 width=22) (actual time=0.097..1,180.625 rows=128,717 loops=1)

45. 10.179 21.998 ↓ 219.5 9,656 1

Nested Loop (cost=2.78..509.89 rows=44 width=22) (actual time=0.079..21.998 rows=9,656 loops=1)

46. 2.199 2.199 ↑ 1.0 2 1

Seq Scan on course c (cost=0..331.94 rows=2 width=8) (actual time=0.017..2.199 rows=2 loops=1)

  • Filter: ((coursetype)::text = 'booking'::text)
47. 8.282 9.620 ↓ 102.7 4,828 2

Bitmap Heap Scan on activity a (cost=2.78..88.51 rows=47 width=30) (actual time=0.688..4.81 rows=4,828 loops=2)

48. 1.338 1.338 ↓ 102.7 4,828 2

Bitmap Index Scan on activity_courseid_idx (cost=0..2.77 rows=47 width=0) (actual time=0.669..0.669 rows=4,828 loops=2)

  • Index Cond: ((courseid)::text = (c.id)::text)
49. 1,023.536 1,023.536 ↑ 1.5 13 9,656

Index Scan using activitytime_activityid_idx on activitytime at (cost=0.42..0.95 rows=19 width=41) (actual time=0.08..0.106 rows=13 loops=9,656)

  • Index Cond: ((activityid)::text = (a.id)::text)
50. 1,802.038 1,802.038 ↑ 6.0 1 128,717

Index Scan using reservation_activitytimeid_idx on reservation re (cost=0.42..0.56 rows=6 width=47) (actual time=0.013..0.014 rows=1 loops=128,717)

  • Index Cond: ((activitytimeid)::text = (at.id)::text)
51. 0.207 0.207 ↑ 1.0 1 3

Index Only Scan using reservation_room_pkey on reservation_room rr (cost=0.42..0.46 rows=1 width=33) (actual time=0.067..0.069 rows=1 loops=3)

  • Index Cond: ((activitytimeid = (re.activitytimeid)::text) AND (weeknumberid = re.weeknumberid))