explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7jXQ

Settings
# exclusive inclusive rows x rows loops node
1. 16.207 62,074.170 ↓ 1.5 4,960 1

Sort (cost=185,676.71..185,685 rows=3,315 width=814) (actual time=62,072.06..62,074.17 rows=4,960 loops=1)

  • Sort Key: s.name, stbefore.during
  • Sort Method: quicksort Memory: 1590kB
2.          

CTE studtimes

3. 307.675 2,297.659 ↓ 8.3 213,833 1

Hash Join (cost=15,957.91..46,725.39 rows=25,814 width=147) (actual time=279.831..2,297.659 rows=213,833 loops=1)

4. 440.029 1,989.952 ↓ 8.3 213,833 1

Nested Loop (cost=15,956.62..46,505.17 rows=25,814 width=75) (actual time=279.784..1,989.952 rows=213,833 loops=1)

5. 0.000 694.591 ↓ 16.1 213,833 1

Gather (cost=15,956.19..40,137.23 rows=13,296 width=92) (actual time=279.729..694.591 rows=213,833 loops=1)

6. 92.573 1,177.917 ↓ 12.9 71,278 3

Hash Join (cost=14,956.19..37,807.63 rows=5,540 width=92) (actual time=275.714..1,177.917 rows=71,278 loops=3)

7. 89.097 1,085.288 ↓ 12.9 71,278 3

Hash Join (cost=14,954.83..37,787.87 rows=5,540 width=74) (actual time=275.576..1,085.288 rows=71,278 loops=3)

8. 91.655 995.996 ↓ 12.9 71,278 3

Hash Join (cost=14,950.33..37,768.28 rows=5,540 width=73) (actual time=275.363..995.996 rows=71,278 loops=3)

9. 279.824 901.673 ↓ 12.9 71,278 3

Nested Loop (cost=14,866.01..37,669.37 rows=5,540 width=78) (actual time=272.677..901.673 rows=71,278 loops=3)

10. 207.206 621.842 ↑ 1.0 24,822 3

Hash Join (cost=14,865.58..25,493.09 rows=25,712 width=48) (actual time=268.303..621.842 rows=24,822 loops=3)

11. 151.338 151.338 ↑ 1.2 253,155 3

Seq Scan on reservation_room rr (cost=0..9,333.9 rows=316,390 width=32) (actual time=0.007..151.338 rows=253,155 loops=3)

12. 5.855 263.298 ↑ 2.9 5,938 3

Hash (cost=14,650.23..14,650.23 rows=17,228 width=16) (actual time=263.298..263.298 rows=5,938 loops=3)

13. 138.347 257.443 ↑ 2.9 5,938 3

Hash Join (cost=2,540.89..14,650.23 rows=17,228 width=16) (actual time=113.598..257.443 rows=5,938 loops=3)

14. 106.626 106.626 ↑ 1.3 169,413 3

Seq Scan on activitytime at (cost=0..11,552.86 rows=211,986 width=30) (actual time=0.01..106.626 rows=169,413 loops=3)

15. 2.365 12.470 ↑ 1.8 2,293 3

Hash (cost=2,489.04..2,489.04 rows=4,148 width=15) (actual time=12.469..12.47 rows=2,293 loops=3)

16. 10.105 10.105 ↑ 1.8 2,293 3

Seq Scan on activity a (cost=0..2,489.04 rows=4,148 width=15) (actual time=0.145..10.105 rows=2,293 loops=3)

  • Filter: ((semesterid)::text = '19h'::text)
17. 0.007 0.007 ↓ 3.0 3 74,465

Index Only Scan using reservation_student_pkey on reservation_student rs (cost=0.42..0.46 rows=1 width=30) (actual time=0.005..0.007 rows=3 loops=74,465)

  • Index Cond: ((activitytimeid = (rr.activitytimeid)::text) AND (weeknumberid = rr.weeknumberid))
18. 1.238 2.668 ↑ 1.0 1,570 3

Hash (cost=64.7..64.7 rows=1,570 width=13) (actual time=2.667..2.668 rows=1,570 loops=3)

19. 1.430 1.430 ↑ 1.0 1,570 3

Seq Scan on room ro (cost=0..64.7 rows=1,570 width=13) (actual time=0.016..1.43 rows=1,570 loops=3)

20. 0.103 0.195 ↑ 1.0 111 3

Hash (cost=3.11..3.11 rows=111 width=9) (actual time=0.194..0.195 rows=111 loops=3)

21. 0.092 0.092 ↑ 1.0 111 3

Seq Scan on building b (cost=0..3.11 rows=111 width=9) (actual time=0.015..0.092 rows=111 loops=3)

22. 0.022 0.056 ↑ 1.0 16 3

Hash (cost=1.16..1.16 rows=16 width=23) (actual time=0.056..0.056 rows=16 loops=3)

23. 0.034 0.034 ↑ 1.0 16 3

Seq Scan on area ar (cost=0..1.16 rows=16 width=23) (actual time=0.022..0.034 rows=16 loops=3)

24. 855.332 855.332 ↑ 1.0 1 213,833

Index Scan using reservation_pkey on reservation r (cost=0.42..0.48 rows=1 width=45) (actual time=0.004..0.004 rows=1 loops=213,833)

  • Index Cond: (((activitytimeid)::text = (rs.activitytimeid)::text) AND (weeknumberid = rs.weeknumberid))
  • Filter: (lower(during) > now())
25. 0.014 0.032 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=20) (actual time=0.032..0.032 rows=13 loops=1)

26. 0.018 0.018 ↑ 1.0 13 1

Seq Scan on campus c (cost=0..1.13 rows=13 width=20) (actual time=0.008..0.018 rows=13 loops=1)

27. 11.366 62,057.963 ↓ 1.5 4,960 1

Merge Join (cost=4,816.11..138,757.48 rows=3,315 width=814) (actual time=3,459.653..62,057.963 rows=4,960 loops=1)

28. 36,078.146 62,039.585 ↓ 1.5 4,960 1

Merge Join (cost=4,815.83..138,217.42 rows=3,315 width=768) (actual time=3,459.597..62,039.585 rows=4,960 loops=1)

29. 429.761 3,145.457 ↓ 8.3 213,833 1

Sort (cost=2,407.91..2,472.45 rows=25,814 width=562) (actual time=3,004.177..3,145.457 rows=213,833 loops=1)

  • Sort Key: stbefore.studentgroupnr
  • Sort Method: external merge Disk: 22224kB
30. 2,715.696 2,715.696 ↓ 8.3 213,833 1

CTE Scan on studtimes stbefore (cost=0..516.28 rows=25,814 width=562) (actual time=279.837..2,715.696 rows=213,833 loops=1)

31. 22,666.573 22,815.982 ↓ 1,484.7 38,325,321 1

Sort (cost=2,407.91..2,472.45 rows=25,814 width=562) (actual time=452.914..22,815.982 rows=38,325,321 loops=1)

  • Sort Key: stafter.studentgroupnr
  • Sort Method: external sort Disk: 23064kB
32. 149.409 149.409 ↓ 8.3 213,833 1

CTE Scan on studtimes stafter (cost=0..516.28 rows=25,814 width=562) (actual time=0.032..149.409 rows=213,833 loops=1)

33. 7.012 7.012 ↑ 1.4 5,415 1

Index Scan using studentgroup_pkey on studentgroup s (cost=0.28..454.62 rows=7,661 width=38) (actual time=0.029..7.012 rows=5,415 loops=1)