explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AXbv

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 355.827 ↓ 1.5 9 1

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

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

GroupAggregate (cost=42,956.14..42,965.56 rows=6 width=905) (actual time=347.288..355.799 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.654 346.928 ↓ 70.5 423 1

Sort (cost=42,956.14..42,956.15 rows=6 width=850) (actual time=346.873..346.928 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.212 345.274 ↓ 70.5 423 1

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

5. 0.145 344.216 ↓ 70.5 423 1

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

6. 0.012 343.774 ↓ 9.0 9 1

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

7. 0.007 343.744 ↓ 9.0 9 1

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

8. 0.021 343.710 ↓ 9.0 9 1

Nested Loop Left Join (cost=40,118.94..42,949.20 rows=1 width=747) (actual time=343.672..343.710 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.495 343.662 ↓ 9.0 9 1

Merge Right Join (cost=40,118.80..42,949.00 rows=1 width=730) (actual time=343.644..343.662 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.324 314.253 ↑ 1.3 74,129 1

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

11. 32.843 297.929 ↑ 1.3 74,129 1

Sort (cost=37,898.13..38,133.97 rows=94,339 width=69) (actual time=288.704..297.929 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.403 265.086 ↑ 1.2 76,743 1

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

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

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

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

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

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

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

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

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

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

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

19. 2.038 9.980 ↓ 1.0 10,579 1

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

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

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

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

22. 1.445 3.757 ↑ 1.0 10,386 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 615kB
23. 2.312 2.312 ↑ 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.312 rows=10,386 loops=1)

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

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

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

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

26. 0.011 22.844 ↓ 9.0 9 1

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

27. 0.011 22.815 ↓ 9.0 9 1

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

28. 0.007 22.786 ↓ 9.0 9 1

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

29. 0.014 22.752 ↓ 9.0 9 1

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

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

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

31. 1.139 22.624 ↓ 9.0 9 1

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

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

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

33. 0.005 18.694 ↓ 4.5 9 1

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

34. 0.004 18.677 ↑ 1.0 1 1

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

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

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

36. 18.624 18.624 ↑ 1.0 1 1

Seq Scan on mdl_user auser (cost=0.00..2,004.71 rows=1 width=59) (actual time=2.594..18.624 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.023 ↑ 1.0 2 1

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

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

Bitmap Index Scan on mdl_dpplan_use_ix (cost=0.00..4.30 rows=2 width=0) (actual time=0.014..0.014 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.079 2.754 ↑ 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.083..0.306 rows=1,828 loops=9)

  • Recheck Cond: (fieldid = 2)
  • Heap Blocks: exact=792
43. 0.675 0.675 ↑ 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.075..0.075 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.846 0.846 ↑ 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.002..0.002 rows=1 loops=423)

  • Index Cond: (id = facetoface_sessions.roomid)
Planning time : 8.911 ms
Execution time : 358.214 ms