explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fZU5

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 380.504 ↑ 177.1 13 1

Sort (cost=57,724.10..57,729.85 rows=2,302 width=905) (actual time=380.503..380.504 rows=13 loops=1)

  • Sort Key: course.shortname, (min(base.id))
  • Sort Method: quicksort Memory: 38kB
2. 8.962 380.471 ↑ 177.1 13 1

GroupAggregate (cost=53,981.41..57,595.55 rows=2,302 width=905) (actual time=371.722..380.471 rows=13 loops=1)

  • Group Key: course.id, course_2.data, ((((auser.firstname)::text || ' '::text) || (auser.lastname)::text)), auser.id, user_27.data, user_26.data, (CASE WHEN (manager.id IS NULL) THEN NULL::text ELSE (((manager.firstname)::text || ' '::text) || (manager.lastname)::text) END), manager.firstnamephonetic, manager.lastnamephonetic, manager.middlename, manager.alternatename, manager.firstname, manager.lastname, atemplate.fullname, (CASE WHEN (((max(CASE WHEN (fss.statuscode IS NULL) THEN '0'::bigint ELSE fss.statuscode END)) IS NULL) OR ((max(CASE WHEN (fss.statuscode IS NULL) THEN '0'::bigint ELSE fss.statuscode END)) = 110)) THEN '0'::bigint ELSE (max(CASE WHEN (fss.statuscode IS NULL) THEN '0'::bigint ELSE fss.statuscode END)) END), course_completions.status, course_category.name
3. 1.607 371.509 ↑ 5.4 427 1

Sort (cost=53,981.41..53,987.16 rows=2,302 width=850) (actual time=371.454..371.509 rows=427 loops=1)

  • Sort Key: course.id, course_2.data, ((((auser.firstname)::text || ' '::text) || (auser.lastname)::text)), auser.id, user_27.data, user_26.data, (CASE WHEN (manager.id IS NULL) THEN NULL::text ELSE (((manager.firstname)::text || ' '::text) || (manager.lastname)::text) END), manager.firstnamephonetic, manager.lastnamephonetic, manager.middlename, manager.alternatename, manager.firstname, manager.lastname, atemplate.fullname, (CASE WHEN (((max(CASE WHEN (fss.statuscode IS NULL) THEN '0'::bigint ELSE fss.statuscode END)) IS NULL) OR ((max(CASE WHEN (fss.statuscode IS NULL) THEN '0'::bigint ELSE fss.statuscode END)) = 110)) THEN '0'::bigint ELSE (max(CASE WHEN (fss.statuscode IS NULL) THEN '0'::bigint ELSE fss.statuscode END)) END), course_completions.status, course_category.name
  • Sort Method: quicksort Memory: 245kB
4. 0.269 369.902 ↑ 5.4 427 1

Hash Left Join (cost=42,596.43..53,852.86 rows=2,302 width=850) (actual time=369.096..369.902 rows=427 loops=1)

  • Hash Cond: (facetoface_sessions.roomid = facetoface_rooms.id)
5. 0.144 367.746 ↑ 5.4 427 1

Nested Loop Left Join (cost=42,368.08..53,589.69 rows=2,302 width=761) (actual time=367.191..367.746 rows=427 loops=1)

6. 0.010 367.342 ↑ 28.9 13 1

Nested Loop Left Join (cost=42,367.80..52,972.39 rows=376 width=761) (actual time=367.180..367.342 rows=13 loops=1)

7. 0.006 367.306 ↑ 28.9 13 1

Nested Loop Left Join (cost=42,367.51..52,683.33 rows=376 width=747) (actual time=367.172..367.306 rows=13 loops=1)

8. 0.017 367.261 ↑ 28.9 13 1

Hash Left Join (cost=42,367.09..51,810.34 rows=376 width=747) (actual time=367.155..367.261 rows=13 loops=1)

  • Hash Cond: (course.category = course_category.id)
9. 0.037 367.208 ↑ 28.9 13 1

Hash Left Join (cost=42,360.04..51,802.26 rows=376 width=730) (actual time=367.108..367.208 rows=13 loops=1)

  • Hash Cond: (auser.id = position_assignment.userid)
10. 0.011 362.312 ↑ 28.9 13 1

Nested Loop Left Join (cost=41,755.14..51,196.37 rows=376 width=722) (actual time=362.218..362.312 rows=13 loops=1)

11. 0.019 362.275 ↑ 28.9 13 1

Nested Loop Left Join (cost=41,754.72..48,019.17 rows=376 width=705) (actual time=362.211..362.275 rows=13 loops=1)

12. 8.619 362.204 ↑ 28.9 13 1

Merge Left Join (cost=41,754.30..44,841.97 rows=376 width=688) (actual time=362.181..362.204 rows=13 loops=1)

  • Merge Cond: ((aplan.userid = fsu.userid) AND (facetoface.id = fs2.facetoface))
13. 0.026 20.761 ↑ 28.9 13 1

Sort (cost=3,711.33..3,712.27 rows=376 width=688) (actual time=20.759..20.761 rows=13 loops=1)

  • Sort Key: aplan.userid, facetoface.id
  • Sort Method: quicksort Memory: 28kB
14. 0.016 20.735 ↑ 28.9 13 1

Hash Left Join (cost=2,637.52..3,695.25 rows=376 width=688) (actual time=20.269..20.735 rows=13 loops=1)

  • Hash Cond: (course_module.instance = facetoface.id)
15. 0.025 20.242 ↑ 28.9 13 1

Nested Loop Left Join (cost=2,516.49..3,573.22 rows=376 width=688) (actual time=19.781..20.242 rows=13 loops=1)

  • Join Filter: (course.id = course_completions.course)
  • Rows Removed by Join Filter: 126
16. 0.012 20.139 ↑ 28.9 13 1

Hash Join (cost=2,516.06..2,639.21 rows=376 width=686) (actual time=19.756..20.139 rows=13 loops=1)

  • Hash Cond: (aplan.templateid = atemplate.id)
17. 0.176 20.112 ↑ 28.9 13 1

Hash Right Join (cost=2,514.93..2,636.38 rows=376 width=178) (actual time=19.733..20.112 rows=13 loops=1)

  • Hash Cond: (course_2.courseid = course.id)
18. 0.336 0.418 ↑ 1.0 1,828 1

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

  • Recheck Cond: (fieldid = 2)
  • Heap Blocks: exact=88
19. 0.082 0.082 ↑ 1.0 1,828 1

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

  • Index Cond: (fieldid = 2)
20. 0.009 19.518 ↑ 28.9 13 1

Hash (cost=2,471.78..2,471.78 rows=376 width=168) (actual time=19.518..19.518 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 0.143 19.509 ↑ 28.9 13 1

Hash Right Join (cost=2,323.01..2,471.78 rows=376 width=168) (actual time=18.990..19.509 rows=13 loops=1)

  • Hash Cond: (course_module.course = course.id)
22. 0.540 0.540 ↑ 1.0 1,744 1

Seq Scan on mdl_course_modules course_module (cost=0.00..138.65 rows=1,744 width=16) (actual time=0.010..0.540 rows=1,744 loops=1)

  • Filter: (module = 8)
  • Rows Removed by Filter: 2628
23. 0.011 18.826 ↑ 28.9 13 1

Hash (cost=2,318.31..2,318.31 rows=376 width=160) (actual time=18.825..18.826 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
24. 0.013 18.815 ↑ 28.9 13 1

Nested Loop Left Join (cost=0.99..2,318.31 rows=376 width=160) (actual time=2.758..18.815 rows=13 loops=1)

25. 0.006 18.776 ↑ 28.9 13 1

Nested Loop (cost=0.71..2,206.36 rows=376 width=91) (actual time=2.750..18.776 rows=13 loops=1)

26. 0.007 18.756 ↑ 21.0 2 1

Nested Loop (cost=0.29..2,156.65 rows=42 width=83) (actual time=2.742..18.756 rows=2 loops=1)

27. 18.734 18.734 ↑ 31.0 1 1

Seq Scan on mdl_user auser (cost=0.00..1,809.18 rows=31 width=59) (actual time=2.722..18.734 rows=1 loops=1)

  • Filter: ((((firstname)::text || ' '::text) || (lastname)::text) ~~* '%24163%'::text)
  • Rows Removed by Filter: 19552
28. 0.015 0.015 ↑ 1.0 2 1

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

  • Index Cond: (userid = auser.id)
29. 0.014 0.014 ↑ 2.2 6 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.007 rows=6 loops=2)

  • Index Cond: (planid = aplan.id)
30. 0.026 0.026 ↑ 1.0 1 13

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)
31. 0.004 0.015 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.011 0.011 ↑ 1.0 6 1

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

33. 0.078 0.078 ↑ 3.6 10 13

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.006 rows=10 loops=13)

  • Index Cond: (aplan.userid = userid)
34. 0.199 0.477 ↑ 1.0 1,735 1

Hash (cost=99.35..99.35 rows=1,735 width=8) (actual time=0.477..0.477 rows=1,735 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
35. 0.278 0.278 ↑ 1.0 1,735 1

Seq Scan on mdl_facetoface facetoface (cost=0.00..99.35 rows=1,735 width=8) (actual time=0.006..0.278 rows=1,735 loops=1)

36. 8.060 332.824 ↑ 1.3 75,347 1

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

37. 17.451 324.764 ↑ 1.3 75,347 1

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

38. 38.919 307.313 ↑ 1.3 75,347 1

Sort (cost=38,042.97..38,280.26 rows=94,918 width=69) (actual time=298.241..307.313 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
39. 64.975 268.394 ↑ 1.2 78,353 1

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

  • Group Key: fsu.userid, fs2.facetoface, fsd.sessiontimezone
40. 68.176 203.419 ↓ 1.0 96,781 1

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

  • Sort Key: fsu.userid, fs2.facetoface, fsd.sessiontimezone
  • Sort Method: external merge Disk: 6208kB
41. 26.403 135.243 ↓ 1.0 96,781 1

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

  • Hash Cond: (fsu.sessionid = fs2.id)
42. 43.637 98.959 ↑ 1.0 94,795 1

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

  • Hash Cond: (fss.signupid = fsu.id)
43. 25.236 25.236 ↑ 1.0 94,795 1

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

  • Filter: (superceded = 0)
  • Rows Removed by Filter: 117582
44. 17.326 30.086 ↑ 1.0 94,795 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3103kB
45. 12.760 12.760 ↑ 1.0 94,795 1

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

46. 1.828 9.881 ↓ 1.0 10,817 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 945kB
47. 3.628 8.053 ↓ 1.0 10,817 1

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

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

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

49. 1.421 3.389 ↑ 1.0 10,624 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 647kB
50. 1.968 1.968 ↑ 1.0 10,624 1

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

51. 0.052 0.052 ↑ 1.0 1 13

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.004..0.004 rows=1 loops=13)

  • Index Cond: ((userid = auser.id) AND (fieldid = 27))
52. 0.026 0.026 ↑ 1.0 1 13

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))
53. 1.803 4.859 ↑ 1.0 13,916 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 777kB
54. 3.056 3.056 ↑ 1.0 13,916 1

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

  • Filter: (type = 1)
55. 0.015 0.036 ↑ 1.0 91 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
56. 0.021 0.021 ↑ 1.0 91 1

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

57. 0.039 0.039 ↑ 1.0 1 13

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.003..0.003 rows=1 loops=13)

  • Index Cond: (id = position_assignment.reportstoid)
  • Filter: (roleid = 9)
58. 0.026 0.026 ↑ 1.0 1 13

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

  • Index Cond: (id = manager_role_assignment.userid)
59. 0.260 0.260 ↓ 4.6 32 13

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.002..0.020 rows=32 loops=13)

  • Index Cond: (facetoface = facetoface.id)
60. 1.027 1.887 ↑ 1.0 6,060 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 578kB
61. 0.860 0.860 ↑ 1.0 6,060 1

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

Planning time : 5.698 ms
Execution time : 382.966 ms