explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k3dD

Settings
# exclusive inclusive rows x rows loops node
1. 16.748 22,021.451 ↓ 1.5 4,960 1

Sort (cost=51,884.54..51,892.83 rows=3,315 width=814) (actual time=22,019.271..22,021.451 rows=4,960 loops=1)

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

CTE studtimes

3. 302.706 2,249.107 ↓ 8.3 213,847 1

Hash Join (cost=15,955.29..46,722.76 rows=25,814 width=147) (actual time=276.335..2,249.107 rows=213,847 loops=1)

4. 434.022 1,946.370 ↓ 8.3 213,847 1

Nested Loop (cost=15,954..46,502.54 rows=25,814 width=75) (actual time=276.279..1,946.37 rows=213,847 loops=1)

5. 0.000 656.960 ↓ 16.1 213,847 1

Gather (cost=15,953.57..40,134.6 rows=13,296 width=92) (actual time=276.206..656.96 rows=213,847 loops=1)

6. 91.792 1,148.112 ↓ 12.9 71,282 3

Hash Join (cost=14,953.57..37,805 rows=5,540 width=92) (actual time=263.799..1,148.112 rows=71,282 loops=3)

7. 87.808 1,056.263 ↓ 12.9 71,282 3

Hash Join (cost=14,952.21..37,785.25 rows=5,540 width=74) (actual time=263.663..1,056.263 rows=71,282 loops=3)

8. 90.785 968.297 ↓ 12.9 71,282 3

Hash Join (cost=14,947.72..37,765.65 rows=5,540 width=73) (actual time=263.486..968.297 rows=71,282 loops=3)

9. 277.307 875.051 ↓ 12.9 71,282 3

Nested Loop (cost=14,863.39..37,666.74 rows=5,540 width=78) (actual time=261.004..875.051 rows=71,282 loops=3)

10. 199.609 597.737 ↑ 1.0 24,826 3

Hash Join (cost=14,862.97..25,490.46 rows=25,712 width=48) (actual time=256.403..597.737 rows=24,826 loops=3)

11. 145.862 145.862 ↑ 1.2 253,159 3

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

12. 5.840 252.266 ↑ 2.9 5,938 3

Hash (cost=14,647.67..14,647.67 rows=17,224 width=16) (actual time=252.265..252.266 rows=5,938 loops=3)

13. 132.150 246.426 ↑ 2.9 5,938 3

Hash Join (cost=2,540.89..14,647.67 rows=17,224 width=16) (actual time=123.817..246.426 rows=5,938 loops=3)

14. 102.030 102.030 ↑ 1.3 169,412 3

Seq Scan on activitytime at (cost=0..11,550.41 rows=211,941 width=30) (actual time=0.013..102.03 rows=169,412 loops=3)

15. 2.519 12.246 ↑ 1.8 2,293 3

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

16. 9.727 9.727 ↑ 1.8 2,293 3

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

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

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,479)

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

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

19. 1.373 1.373 ↑ 1.0 1,570 3

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

20. 0.071 0.158 ↑ 1.0 111 3

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

21. 0.087 0.087 ↑ 1.0 111 3

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

22. 0.025 0.057 ↑ 1.0 16 3

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

23. 0.032 0.032 ↑ 1.0 16 3

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

24. 855.388 855.388 ↑ 1.0 1 213,847

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,847)

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

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

26. 0.015 0.015 ↑ 1.0 13 1

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

27. 18,712.158 22,004.703 ↓ 1.5 4,960 1

Hash Join (cost=1,221.12..4,967.94 rows=3,315 width=814) (actual time=3,180.926..22,004.703 rows=4,960 loops=1)

28. 410.335 410.335 ↓ 8.3 213,847 1

CTE Scan on studtimes stbefore (cost=0..516.28 rows=25,814 width=562) (actual time=276.339..410.335 rows=213,847 loops=1)

29. 242.112 2,882.210 ↓ 8.3 213,847 1

Hash (cost=898.45..898.45 rows=25,814 width=600) (actual time=2,882.21..2,882.21 rows=213,847 loops=1)

30. 284.823 2,640.098 ↓ 8.3 213,847 1

Hash Join (cost=314.37..898.45 rows=25,814 width=600) (actual time=10.932..2,640.098 rows=213,847 loops=1)

31. 2,344.394 2,344.394 ↓ 8.3 213,847 1

CTE Scan on studtimes stafter (cost=0..516.28 rows=25,814 width=562) (actual time=0.002..2,344.394 rows=213,847 loops=1)

32. 5.357 10.881 ↓ 1.0 7,662 1

Hash (cost=218.61..218.61 rows=7,661 width=38) (actual time=10.88..10.881 rows=7,662 loops=1)

33. 5.524 5.524 ↓ 1.0 7,662 1

Seq Scan on studentgroup s (cost=0..218.61 rows=7,661 width=38) (actual time=0.013..5.524 rows=7,662 loops=1)