explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jw3G

Settings
# exclusive inclusive rows x rows loops node
1. 179.384 366.120 ↑ 3.2 427 1

Gather Merge (cost=50,779.24..50,934.95 rows=1,354 width=850) (actual time=365.940..366.120 rows=427 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2. 0.179 186.736 ↑ 6.3 214 2 / 2

Sort (cost=49,779.23..49,782.62 rows=1,354 width=850) (actual time=186.725..186.736 rows=214 loops=2)

  • Sort Key: course.shortname, base.id
  • Sort Method: quicksort Memory: 245kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
3. 0.140 186.557 ↑ 6.3 214 2 / 2

Hash Left Join (cost=46,069.67..49,708.80 rows=1,354 width=850) (actual time=186.196..186.557 rows=214 loops=2)

  • Hash Cond: (facetoface_sessions.roomid = facetoface_rooms.id)
4. 0.069 185.453 ↑ 6.3 214 2 / 2

Nested Loop Left Join (cost=45,841.32..49,459.98 rows=1,354 width=761) (actual time=185.219..185.453 rows=214 loops=2)

5. 0.013 185.241 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=45,841.03..49,097.14 rows=221 width=761) (actual time=185.212..185.241 rows=6 loops=2)

6. 4.288 185.209 ↑ 36.8 6 2 / 2

Merge Left Join (cost=45,840.74..48,927.25 rows=221 width=747) (actual time=185.197..185.209 rows=6 loops=2)

  • Merge Cond: ((aplan.userid = fsu.userid) AND (facetoface.id = fs2.facetoface))
7. 0.018 12.642 ↑ 36.8 6 2 / 2

Sort (cost=7,797.78..7,798.33 rows=221 width=747) (actual time=12.641..12.642 rows=6 loops=2)

  • Sort Key: aplan.userid, facetoface.id
  • Sort Method: quicksort Memory: 31kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
8. 0.007 12.624 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=788.46..7,789.17 rows=221 width=747) (actual time=9.381..12.624 rows=6 loops=2)

9. 0.009 12.604 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=788.04..7,276.06 rows=221 width=747) (actual time=9.373..12.604 rows=6 loops=2)

10. 0.027 12.582 ↑ 36.8 6 2 / 2

Hash Left Join (cost=787.76..7,165.75 rows=221 width=747) (actual time=9.367..12.582 rows=6 loops=2)

  • Hash Cond: (auser.id = position_assignment.userid)
11. 0.006 9.964 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=182.86..6,560.27 rows=221 width=739) (actual time=6.751..9.964 rows=6 loops=2)

12. 0.005 9.945 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=182.44..4,692.82 rows=221 width=722) (actual time=6.747..9.945 rows=6 loops=2)

13. 0.007 9.921 ↑ 36.8 6 2 / 2

Hash Left Join (cost=182.02..2,825.37 rows=221 width=705) (actual time=6.740..9.921 rows=6 loops=2)

  • Hash Cond: (course.category = course_category.id)
14. 0.009 9.896 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=174.98..2,817.72 rows=221 width=688) (actual time=6.718..9.896 rows=6 loops=2)

  • Join Filter: (course.id = course_completions.course)
  • Rows Removed by Join Filter: 63
15. 0.005 9.842 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=174.55..2,268.74 rows=221 width=686) (actual time=6.708..9.842 rows=6 loops=2)

16. 0.009 9.818 ↑ 36.8 6 2 / 2

Hash Left Join (cost=174.27..2,143.52 rows=221 width=678) (actual time=6.702..9.818 rows=6 loops=2)

  • Hash Cond: (course.id = course_2.courseid)
17. 0.010 9.447 ↑ 36.8 6 2 / 2

Nested Loop Left Join (cost=2.12..1,966.67 rows=221 width=668) (actual time=6.336..9.447 rows=6 loops=2)

18. 0.004 9.424 ↑ 36.8 6 2 / 2

Nested Loop (cost=1.84..1,900.87 rows=221 width=599) (actual time=6.332..9.424 rows=6 loops=2)

19. 0.007 9.412 ↑ 25.0 1 2 / 2

Hash Join (cost=1.42..1,871.28 rows=25 width=591) (actual time=6.327..9.412 rows=1 loops=2)

  • Hash Cond: (aplan.templateid = atemplate.id)
20. 0.004 9.400 ↑ 25.0 1 2 / 2

Nested Loop (cost=0.29..1,870.04 rows=25 width=83) (actual time=6.317..9.400 rows=1 loops=2)

21. 9.389 9.389 ↓ 0.0 0 2 / 2

Parallel Seq Scan on mdl_user auser (cost=0.00..1,668.28 rows=18 width=59) (actual time=6.307..9.389 rows=0 loops=2)

  • Filter: ((((firstname)::text || ' '::text) || (lastname)::text) ~~* '%24163%'::text)
  • Rows Removed by Filter: 9776
22. 0.007 0.007 ↑ 1.0 2 1 / 2

Index Scan using mdl_dpplan_use_ix on mdl_dp_plan aplan (cost=0.29..11.19 rows=2 width=24) (actual time=0.012..0.014 rows=2 loops=1)

  • Index Cond: (userid = auser.id)
23. 0.002 0.005 ↑ 1.0 6 1 / 2

Hash (cost=1.06..1.06 rows=6 width=524) (actual time=0.010..0.010 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.003 0.003 ↑ 1.0 6 1 / 2

Seq Scan on mdl_dp_template atemplate (cost=0.00..1.06 rows=6 width=524) (actual time=0.005..0.006 rows=6 loops=1)

25. 0.008 0.008 ↑ 2.2 6 2 / 2

Index Scan using mdl_dpplancourassi_pla_ix on mdl_dp_plan_course_assign base (cost=0.42..1.05 rows=13 width=24) (actual time=0.005..0.008 rows=6 loops=2)

  • Index Cond: (planid = aplan.id)
26. 0.013 0.013 ↑ 1.0 1 13 / 2

Index Scan using mdl_cour_id_pk on mdl_course course (cost=0.28..0.30 rows=1 width=77) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (id = base.courseid)
27. 0.118 0.362 ↑ 1.0 1,828 1 / 2

Hash (cost=149.30..149.30 rows=1,828 width=18) (actual time=0.725..0.725 rows=1,828 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
28. 0.206 0.244 ↑ 1.0 1,828 1 / 2

Bitmap Heap Scan on mdl_course_info_data course_2 (cost=38.45..149.30 rows=1,828 width=18) (actual time=0.091..0.489 rows=1,828 loops=1)

  • Recheck Cond: (fieldid = 2)
  • Heap Blocks: exact=88
29. 0.038 0.038 ↑ 1.0 1,828 1 / 2

Bitmap Index Scan on mdl_courinfodata_fie_ix (cost=0.00..37.99 rows=1,828 width=0) (actual time=0.077..0.077 rows=1,828 loops=1)

  • Index Cond: (fieldid = 2)
30. 0.019 0.019 ↑ 1.0 1 13 / 2

Index Scan using mdl_courmodu_cou_ix on mdl_course_modules course_module (cost=0.28..0.56 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=13)

  • Index Cond: (course = course.id)
  • Filter: (module = 8)
  • Rows Removed by Filter: 1
31. 0.045 0.045 ↑ 3.6 10 13 / 2

Index Scan using mdl_courcomp_use_ix on mdl_course_completions course_completions (cost=0.42..2.03 rows=36 width=18) (actual time=0.003..0.007 rows=10 loops=13)

  • Index Cond: (aplan.userid = userid)
32. 0.008 0.018 ↑ 1.0 91 1 / 2

Hash (cost=5.91..5.91 rows=91 width=33) (actual time=0.035..0.035 rows=91 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
33. 0.009 0.009 ↑ 1.0 91 1 / 2

Seq Scan on mdl_course_categories course_category (cost=0.00..5.91 rows=91 width=33) (actual time=0.005..0.019 rows=91 loops=1)

34. 0.019 0.019 ↑ 1.0 1 13 / 2

Index Scan using mdl_userinfodata_usefie_ix on mdl_user_info_data user_27 (cost=0.42..8.44 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=13)

  • Index Cond: ((userid = auser.id) AND (fieldid = 27))
35. 0.013 0.013 ↑ 1.0 1 13 / 2

Index Scan using mdl_userinfodata_usefie_ix on mdl_user_info_data user_26 (cost=0.42..8.44 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: ((userid = auser.id) AND (fieldid = 26))
36. 1.057 2.591 ↑ 1.0 13,916 1 / 2

Hash (cost=430.95..430.95 rows=13,916 width=16) (actual time=5.183..5.183 rows=13,916 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 777kB
37. 1.534 1.534 ↑ 1.0 13,916 1 / 2

Seq Scan on mdl_pos_assignment position_assignment (cost=0.00..430.95 rows=13,916 width=16) (actual time=0.008..3.069 rows=13,916 loops=1)

  • Filter: (type = 1)
38. 0.013 0.013 ↑ 1.0 1 13 / 2

Index Only Scan using mdl_face_id_pk on mdl_facetoface facetoface (cost=0.28..0.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (id = course_module.instance)
  • Heap Fetches: 9
39. 0.013 0.013 ↑ 1.0 1 13 / 2

Index Scan using mdl_roleassi_id_pk on mdl_role_assignments manager_role_assignment (cost=0.42..2.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (id = position_assignment.reportstoid)
  • Filter: (roleid = 9)
40. 4.049 168.279 ↑ 1.3 75,347 1 / 2

Materialize (cost=38,042.97..40,653.21 rows=94,918 width=24) (actual time=301.730..336.557 rows=75,347 loops=1)

41. 8.799 164.230 ↑ 1.3 75,347 1 / 2

Unique (cost=38,042.97..39,466.74 rows=94,918 width=69) (actual time=301.722..328.460 rows=75,347 loops=1)

42. 19.657 155.430 ↑ 1.3 75,347 1 / 2

Sort (cost=38,042.97..38,280.26 rows=94,918 width=69) (actual time=301.721..310.861 rows=75,347 loops=1)

  • Sort Key: fsu.userid, fs2.facetoface, (string_agg(concat_ws(','::text, fsd.timestart, fsd.timefinish), '|'::text)), fsd.sessiontimezone, (max(CASE WHEN (fss.statuscode IS NULL) THEN '0'::bigint ELSE fss.statuscode END))
  • Sort Method: external merge Disk: 6056kB
43. 32.570 135.773 ↑ 1.2 78,353 1 / 2

GroupAggregate (cost=23,452.87..26,300.41 rows=94,918 width=69) (actual time=190.661..271.546 rows=78,353 loops=1)

  • Group Key: fsu.userid, fs2.facetoface, fsd.sessiontimezone
44. 33.869 103.203 ↓ 1.0 96,781 1 / 2

Sort (cost=23,452.87..23,690.16 rows=94,918 width=53) (actual time=190.642..206.406 rows=96,781 loops=1)

  • Sort Key: fsu.userid, fs2.facetoface, fsd.sessiontimezone
  • Sort Method: external merge Disk: 6208kB
45. 13.516 69.334 ↓ 1.0 96,781 1 / 2

Hash Right Join (cost=4,658.31..12,361.31 rows=94,918 width=53) (actual time=41.883..138.668 rows=96,781 loops=1)

  • Hash Cond: (fsu.sessionid = fs2.id)
46. 21.968 50.309 ↑ 1.0 94,795 1 / 2

Hash Right Join (cost=3,574.89..9,972.77 rows=94,918 width=24) (actual time=30.818..100.619 rows=94,795 loops=1)

  • Hash Cond: (fss.signupid = fsu.id)
47. 13.008 13.008 ↑ 1.0 94,795 1 / 2

Seq Scan on mdl_facetoface_signups_status fss (cost=0.00..4,664.71 rows=94,918 width=16) (actual time=0.008..26.015 rows=94,795 loops=1)

  • Filter: (superceded = 0)
  • Rows Removed by Filter: 117582
48. 9.047 15.334 ↑ 1.0 94,795 1 / 2

Hash (cost=1,833.95..1,833.95 rows=94,795 width=24) (actual time=30.668..30.668 rows=94,795 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3103kB
49. 6.287 6.287 ↑ 1.0 94,795 1 / 2

Seq Scan on mdl_facetoface_signups fsu (cost=0.00..1,833.95 rows=94,795 width=24) (actual time=0.009..12.574 rows=94,795 loops=1)

50. 1.143 5.508 ↓ 1.0 10,817 1 / 2

Hash (cost=950.62..950.62 rows=10,624 width=45) (actual time=11.017..11.017 rows=10,817 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 945kB
51. 1.987 4.366 ↓ 1.0 10,817 1 / 2

Hash Right Join (cost=712.04..950.62 rows=10,624 width=45) (actual time=3.779..8.732 rows=10,817 loops=1)

  • Hash Cond: (fsd.sessionid = fs2.id)
  • Join Filter: (fs2.datetimeknown = 1)
  • Rows Removed by Join Filter: 238
52. 0.521 0.521 ↑ 1.0 10,817 1 / 2

Seq Scan on mdl_facetoface_sessions_dates fsd (cost=0.00..210.17 rows=10,817 width=37) (actual time=0.006..1.043 rows=10,817 loops=1)

53. 0.849 1.857 ↑ 1.0 10,624 1 / 2

Hash (cost=579.24..579.24 rows=10,624 width=18) (actual time=3.715..3.715 rows=10,624 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 647kB
54. 1.009 1.009 ↑ 1.0 10,624 1 / 2

Seq Scan on mdl_facetoface_sessions fs2 (cost=0.00..579.24 rows=10,624 width=18) (actual time=0.009..2.018 rows=10,624 loops=1)

55. 0.019 0.019 ↑ 1.0 1 13 / 2

Index Scan using dt_user_pkey on mdl_user manager (cost=0.29..0.77 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=13)

  • Index Cond: (id = manager_role_assignment.userid)
56. 0.143 0.143 ↓ 4.6 32 13 / 2

Index Scan using mdl_facesess_fac_ix on mdl_facetoface_sessions facetoface_sessions (cost=0.29..1.57 rows=7 width=16) (actual time=0.003..0.022 rows=32 loops=13)

  • Index Cond: (facetoface = facetoface.id)
57. 0.526 0.964 ↑ 1.0 6,060 1 / 2

Hash (cost=152.60..152.60 rows=6,060 width=49) (actual time=1.928..1.928 rows=6,060 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 578kB
58. 0.438 0.438 ↑ 1.0 6,060 1 / 2

Seq Scan on mdl_facetoface_room facetoface_rooms (cost=0.00..152.60 rows=6,060 width=49) (actual time=0.008..0.875 rows=6,060 loops=1)

Planning time : 5.951 ms
Execution time : 368.427 ms