explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GOuX

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 365.863 ↓ 1.5 9 1

Sort (cost=42,965.64..42,965.65 rows=6 width=905) (actual time=365.862..365.863 rows=9 loops=1)

  • Sort Key: course.shortname, (min(base.id))
  • Sort Method: quicksort Memory: 36kB
2. 8.831 365.834 ↓ 1.5 9 1

GroupAggregate (cost=42,956.14..42,965.56 rows=6 width=905) (actual time=357.366..365.834 rows=9 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.661 357.003 ↓ 70.5 423 1

Sort (cost=42,956.14..42,956.15 rows=6 width=850) (actual time=356.942..357.003 rows=423 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: 243kB
4. 0.520 355.342 ↓ 70.5 423 1

Nested Loop Left Join (cost=40,120.22..42,956.06 rows=6 width=850) (actual time=353.903..355.342 rows=423 loops=1)

5. 0.143 354.399 ↓ 70.5 423 1

Nested Loop Left Join (cost=40,119.94..42,953.94 rows=6 width=761) (actual time=353.892..354.399 rows=423 loops=1)

6. 0.011 353.959 ↓ 9.0 9 1

Nested Loop Left Join (cost=40,119.65..42,952.29 rows=1 width=761) (actual time=353.882..353.959 rows=9 loops=1)

7. 0.005 353.930 ↓ 9.0 9 1

Nested Loop Left Join (cost=40,119.37..42,951.52 rows=1 width=747) (actual time=353.872..353.930 rows=9 loops=1)

8. 0.016 353.898 ↓ 9.0 9 1

Nested Loop Left Join (cost=40,118.94..42,949.20 rows=1 width=747) (actual time=353.861..353.898 rows=9 loops=1)

  • Filter: ((((course_category.path)::text !~~ '/58/%'::text) AND ((course_category.path)::text <> '/58'::text)) OR (course_category.path IS NULL))
9. 6.234 353.855 ↓ 9.0 9 1

Merge Right Join (cost=40,118.80..42,949.00 rows=1 width=730) (actual time=353.838..353.855 rows=9 loops=1)

  • Merge Cond: ((fsu.userid = aplan.userid) AND (fs2.facetoface = facetoface.id))
  • Filter: ((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 <> 100) OR (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 IS NULL))
10. 16.143 324.766 ↑ 1.3 74,129 1

Unique (cost=37,898.13..39,313.21 rows=94,339 width=69) (actual time=299.786..324.766 rows=74,129 loops=1)

11. 31.991 308.623 ↑ 1.3 74,129 1

Sort (cost=37,898.13..38,133.97 rows=94,339 width=69) (actual time=299.785..308.623 rows=74,129 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: 5976kB
12. 64.306 276.632 ↑ 1.2 76,743 1

GroupAggregate (cost=23,401.93..26,232.10 rows=94,339 width=69) (actual time=197.141..276.632 rows=76,743 loops=1)

  • Group Key: fsu.userid, fs2.facetoface, fsd.sessiontimezone
13. 65.975 212.326 ↓ 1.0 94,825 1

Sort (cost=23,401.93..23,637.78 rows=94,339 width=53) (actual time=197.123..212.326 rows=94,825 loops=1)

  • Sort Key: fsu.userid, fs2.facetoface, fsd.sessiontimezone
  • Sort Method: external merge Disk: 6144kB
14. 26.809 146.351 ↓ 1.0 94,825 1

Hash Join (cost=4,681.28..12,379.90 rows=94,339 width=53) (actual time=49.770..146.351 rows=94,825 loops=1)

  • Hash Cond: (fsu.sessionid = fs2.id)
15. 42.498 107.263 ↑ 1.0 94,795 1

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

  • Hash Cond: (fss.signupid = fsu.id)
16. 27.378 27.378 ↑ 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.009..27.378 rows=94,795 loops=1)

  • Filter: (superceded = 0)
  • Rows Removed by Filter: 117582
17. 21.662 37.387 ↑ 1.0 94,795 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3103kB
18. 15.725 15.725 ↑ 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..15.725 rows=94,795 loops=1)

19. 2.569 12.279 ↓ 1.0 10,579 1

Hash (cost=974.20..974.20 rows=10,575 width=53) (actual time=12.279..12.279 rows=10,579 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1038kB
20. 3.879 9.710 ↓ 1.0 10,579 1

Hash Join (cost=735.62..974.20 rows=10,575 width=53) (actual time=4.650..9.710 rows=10,579 loops=1)

  • Hash Cond: (fsd.sessionid = fs2.id)
21. 1.211 1.211 ↑ 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.009..1.211 rows=10,817 loops=1)

22. 1.832 4.620 ↑ 1.0 10,386 1

Hash (cost=605.80..605.80 rows=10,386 width=16) (actual time=4.620..4.620 rows=10,386 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 615kB
23. 2.788 2.788 ↑ 1.0 10,386 1

Seq Scan on mdl_facetoface_sessions fs2 (cost=0.00..605.80 rows=10,386 width=16) (actual time=0.007..2.788 rows=10,386 loops=1)

  • Filter: (datetimeknown = 1)
  • Rows Removed by Filter: 238
24. 0.037 22.855 ↓ 9.0 9 1

Sort (cost=2,220.67..2,220.68 rows=1 width=730) (actual time=22.853..22.855 rows=9 loops=1)

  • Sort Key: aplan.userid, facetoface.id
  • Sort Method: quicksort Memory: 29kB
25. 0.007 22.818 ↓ 9.0 9 1

Nested Loop Left Join (cost=45.56..2,220.66 rows=1 width=730) (actual time=3.016..22.818 rows=9 loops=1)

26. 0.012 22.784 ↓ 9.0 9 1

Nested Loop Left Join (cost=45.28..2,220.16 rows=1 width=730) (actual time=3.007..22.784 rows=9 loops=1)

27. 0.011 22.754 ↓ 9.0 9 1

Nested Loop Left Join (cost=45.00..2,211.86 rows=1 width=722) (actual time=2.999..22.754 rows=9 loops=1)

28. 0.009 22.725 ↓ 9.0 9 1

Nested Loop Left Join (cost=44.58..2,203.41 rows=1 width=705) (actual time=2.993..22.725 rows=9 loops=1)

29. 0.016 22.689 ↓ 9.0 9 1

Nested Loop Left Join (cost=44.16..2,194.96 rows=1 width=688) (actual time=2.983..22.689 rows=9 loops=1)

  • Join Filter: (course.id = course_completions.course)
  • Rows Removed by Join Filter: 108
30. 0.006 22.601 ↓ 9.0 9 1

Nested Loop Left Join (cost=43.73..2,192.47 rows=1 width=686) (actual time=2.964..22.601 rows=9 loops=1)

31. 1.138 22.559 ↓ 9.0 9 1

Nested Loop Left Join (cost=43.45..2,191.70 rows=1 width=678) (actual time=2.955..22.559 rows=9 loops=1)

  • Join Filter: (course_2.courseid = course.id)
  • Rows Removed by Join Filter: 16443
32. 0.013 18.658 ↓ 9.0 9 1

Nested Loop (cost=5.00..2,019.55 rows=1 width=668) (actual time=2.674..18.658 rows=9 loops=1)

33. 0.006 18.618 ↓ 4.5 9 1

Nested Loop (cost=4.72..2,018.95 rows=2 width=599) (actual time=2.666..18.618 rows=9 loops=1)

34. 0.004 18.600 ↑ 1.0 1 1

Nested Loop (cost=4.30..2,017.77 rows=1 width=591) (actual time=2.656..18.600 rows=1 loops=1)

  • Join Filter: (aplan.templateid = atemplate.id)
  • Rows Removed by Join Filter: 1
35. 0.008 18.578 ↓ 2.0 2 1

Nested Loop (cost=4.30..2,016.68 rows=1 width=83) (actual time=2.632..18.578 rows=2 loops=1)

36. 18.548 18.548 ↑ 1.0 1 1

Seq Scan on mdl_user auser (cost=0.00..2,004.71 rows=1 width=59) (actual time=2.605..18.548 rows=1 loops=1)

  • Filter: (((((firstname)::text || ' '::text) || (lastname)::text) ~~* '%24163%'::text) AND (CASE WHEN ((deleted = 0) AND (suspended = 1)) THEN 2 ELSE (deleted)::integer END = 0))
  • Rows Removed by Filter: 19552
37. 0.009 0.022 ↑ 1.0 2 1

Bitmap Heap Scan on mdl_dp_plan aplan (cost=4.30..11.95 rows=2 width=24) (actual time=0.020..0.022 rows=2 loops=1)

  • Recheck Cond: (userid = auser.id)
  • Heap Blocks: exact=2
38. 0.013 0.013 ↑ 1.0 2 1

Bitmap Index Scan on mdl_dpplan_use_ix (cost=0.00..4.30 rows=2 width=0) (actual time=0.013..0.013 rows=2 loops=1)

  • Index Cond: (userid = auser.id)
39. 0.018 0.018 ↑ 1.0 1 2

Seq Scan on mdl_dp_template atemplate (cost=0.00..1.07 rows=1 width=524) (actual time=0.009..0.009 rows=1 loops=2)

  • Filter: ((fullname)::text ~~* '%2019-20%'::text)
  • Rows Removed by Filter: 5
40. 0.012 0.012 ↑ 1.4 9 1

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.007..0.012 rows=9 loops=1)

  • Index Cond: (planid = aplan.id)
41. 0.027 0.027 ↑ 1.0 1 9

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

  • Index Cond: (id = base.courseid)
  • Filter: (coursetype = 2)
42. 2.097 2.763 ↑ 1.0 1,828 9

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

  • Recheck Cond: (fieldid = 2)
  • Heap Blocks: exact=792
43. 0.666 0.666 ↑ 1.0 1,828 9

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

  • Index Cond: (fieldid = 2)
44. 0.036 0.036 ↑ 1.0 1 9

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

  • Index Cond: (course = course.id)
  • Filter: (module = 8)
  • Rows Removed by Filter: 0
45. 0.072 0.072 ↑ 3.0 12 9

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.008 rows=12 loops=9)

  • Index Cond: (aplan.userid = userid)
46. 0.027 0.027 ↑ 1.0 1 9

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=9)

  • Index Cond: ((userid = auser.id) AND (fieldid = 27))
47. 0.018 0.018 ↑ 1.0 1 9

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=9)

  • Index Cond: ((userid = auser.id) AND (fieldid = 26))
48. 0.018 0.018 ↑ 1.0 1 9

Index Scan using mdl_posassi_usetyp_uix on mdl_pos_assignment position_assignment (cost=0.29..8.30 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=9)

  • Index Cond: ((userid = auser.id) AND (type = 1))
49. 0.027 0.027 ↑ 1.0 1 9

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

  • Index Cond: (id = course_module.instance)
  • Heap Fetches: 9
50. 0.027 0.027 ↑ 1.0 1 9

Index Scan using mdl_courcate_id_pk on mdl_course_categories course_category (cost=0.14..0.19 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=9)

  • Index Cond: (id = course.category)
51. 0.027 0.027 ↑ 1.0 1 9

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=9)

  • Index Cond: (id = position_assignment.reportstoid)
  • Filter: (roleid = 9)
52. 0.018 0.018 ↑ 1.0 1 9

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=9)

  • Index Cond: (id = manager_role_assignment.userid)
53. 0.297 0.297 ↓ 6.7 47 9

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.033 rows=47 loops=9)

  • Index Cond: (facetoface = facetoface.id)
54. 0.423 0.423 ↑ 1.0 1 423

Index Scan using mdl_faceroom_id_pk on mdl_facetoface_room facetoface_rooms (cost=0.28..0.34 rows=1 width=49) (actual time=0.001..0.001 rows=1 loops=423)

  • Index Cond: (id = facetoface_sessions.roomid)
Planning time : 10.807 ms
Execution time : 368.255 ms