explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IO43

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 48.973 ↓ 39.0 39 1

Unique (cost=1,883.01..1,883.07 rows=1 width=683) (actual time=48.938..48.973 rows=39 loops=1)

2. 0.226 48.938 ↓ 73.0 73 1

Sort (cost=1,883.01..1,883.02 rows=1 width=683) (actual time=48.938..48.938 rows=73 loops=1)

  • Sort Key: ucrs.course_id, ucrs.term_code, uco.offer_type_code, usr.idnumber, (CASE WHEN ((mods.name)::text = 'quiz'::text) THEN (SubPlan 1) WHEN ((mods.name)::text = 'assign'::text) THEN (SubPlan 2) WHEN ((mods.name)::text = 'lesson'::text) THEN (SubPlan 3) WHEN ((mods.name)::text = 'forum'::text) THEN (SubPlan 4) WHEN ((mods.name)::text = 'scorm'::text) THEN (SubPlan 5) WHEN ((mods.name)::text = 'data'::text) THEN (SubPlan 6) ELSE NULL::bigint END), ucrs.subject_area_code, ucrs.catalogue_number, ucrs.moodle_course_id, mods.name, cmod.id, (COALESCE(qz.timeclose, asgn.duedate, scrm.timeclose, dta.timeavailableto, frm.assesstimefinish, lsn.deadline)), ((SubPlan 7)), ((SubPlan 8)), (CASE WHEN (uass.assessment_weighting = '-1'::numeric) THEN round((uass.category_weighting / ((SubPlan 9))::numeric), 2) ELSE round(((uass.assessment_weighting * uass.category_weighting) / '100'::numeric), 2) END), ptf.externalid, ptf.similarityscore, (COALESCE(qz.timeopen, asgn.allowsubmissionsfromdate, scrm.timeopen, dta.timeavailablefrom, frm.assesstimestart, lsn.available)), (COALESCE(asgn.cutoffdate)), grdl.letter, uass.category_id
  • Sort Method: quicksort Memory: 44kB
3. 0.401 48.712 ↓ 73.0 73 1

Nested Loop Left Join (cost=22.53..1,883.00 rows=1 width=683) (actual time=3.881..48.712 rows=73 loops=1)

  • Join Filter: (grdl.lowerboundary = (SubPlan 10))
  • Rows Removed by Join Filter: 439
4. 0.075 43.027 ↓ 73.0 73 1

Nested Loop Left Join (cost=22.53..1,763.27 rows=1 width=253) (actual time=3.766..43.027 rows=73 loops=1)

  • Join Filter: (grd.userid = usr.id)
5. 0.343 42.733 ↓ 73.0 73 1

Nested Loop Left Join (cost=22.11..1,760.97 rows=1 width=256) (actual time=3.757..42.733 rows=73 loops=1)

  • Join Filter: (grit.iteminstance = cmod.instance)
  • Rows Removed by Join Filter: 2798
6. 0.680 41.076 ↓ 73.0 73 1

Nested Loop Left Join (cost=21.82..1,756.44 rows=1 width=241) (actual time=3.750..41.076 rows=73 loops=1)

  • Join Filter: ((ptf.identifier)::text = (fils.pathnamehash)::text)
  • Rows Removed by Join Filter: 10996
7. 0.122 36.016 ↓ 73.0 73 1

Nested Loop Left Join (cost=21.53..1,752.37 rows=1 width=264) (actual time=3.651..36.016 rows=73 loops=1)

  • Join Filter: ((fils.userid = usr.id) AND (fils.itemid = asub.id))
  • Rows Removed by Join Filter: 1281
8. 0.069 34.361 ↓ 73.0 73 1

Nested Loop Left Join (cost=21.11..1,579.89 rows=1 width=231) (actual time=3.589..34.361 rows=73 loops=1)

  • Join Filter: (asub.userid = usr.id)
9. 0.106 34.073 ↓ 73.0 73 1

Nested Loop (cost=20.69..1,578.01 rows=1 width=231) (actual time=3.579..34.073 rows=73 loops=1)

10. 0.058 33.546 ↓ 210.5 421 1

Nested Loop (cost=20.40..1,576.98 rows=2 width=239) (actual time=3.576..33.546 rows=421 loops=1)

11. 0.049 33.323 ↓ 55.0 55 1

Nested Loop (cost=20.12..1,576.40 rows=1 width=239) (actual time=3.574..33.323 rows=55 loops=1)

12. 0.390 33.164 ↓ 55.0 55 1

Nested Loop (cost=19.84..1,572.09 rows=1 width=232) (actual time=3.567..33.164 rows=55 loops=1)

13. 1.096 30.622 ↓ 2,152.0 2,152 1

Nested Loop (cost=19.55..1,571.57 rows=1 width=232) (actual time=0.226..30.622 rows=2,152 loops=1)

14. 0.000 28.254 ↓ 1,272.0 1,272 1

Nested Loop Left Join (cost=19.27..1,571.16 rows=1 width=232) (actual time=0.222..28.254 rows=1,272 loops=1)

15. 0.968 27.198 ↓ 1,272.0 1,272 1

Nested Loop Left Join (cost=19.12..1,570.96 rows=1 width=216) (actual time=0.220..27.198 rows=1,272 loops=1)

16. 0.248 24.958 ↓ 1,272.0 1,272 1

Nested Loop Left Join (cost=18.84..1,569.32 rows=1 width=200) (actual time=0.215..24.958 rows=1,272 loops=1)

17. 0.954 23.438 ↓ 1,272.0 1,272 1

Nested Loop Left Join (cost=18.56..1,568.76 rows=1 width=184) (actual time=0.214..23.438 rows=1,272 loops=1)

18. 0.848 22.484 ↓ 1,272.0 1,272 1

Nested Loop (cost=18.42..1,568.57 rows=1 width=168) (actual time=0.212..22.484 rows=1,272 loops=1)

19. 1.016 20.366 ↓ 21.2 1,270 1

Nested Loop Left Join (cost=18.14..1,545.80 rows=60 width=142) (actual time=0.209..20.366 rows=1,270 loops=1)

20. 0.997 18.080 ↓ 21.2 1,270 1

Nested Loop Left Join (cost=17.86..1,513.71 rows=60 width=110) (actual time=0.205..18.080 rows=1,270 loops=1)

21. 0.287 15.813 ↓ 21.2 1,270 1

Nested Loop (cost=17.58..1,464.77 rows=60 width=94) (actual time=0.200..15.813 rows=1,270 loops=1)

22. 1.071 14.256 ↓ 21.2 1,270 1

Nested Loop (cost=17.44..1,450.72 rows=60 width=86) (actual time=0.198..14.256 rows=1,270 loops=1)

  • Join Filter: (ucrs.moodle_course_id = mcrs.id)
23. 0.441 11.788 ↓ 11.8 1,397 1

Hash Join (cost=17.16..1,395.60 rows=118 width=78) (actual time=0.166..11.788 rows=1,397 loops=1)

  • Hash Cond: (cmod.course = ucrs.moodle_course_id)
24. 0.128 11.216 ↓ 7.3 1,398 1

Nested Loop (cost=0.56..1,377.10 rows=192 width=40) (actual time=0.029..11.216 rows=1,398 loops=1)

25. 0.029 0.054 ↑ 1.0 6 1

Nested Loop Semi Join (cost=0.14..17.11 rows=6 width=16) (actual time=0.013..0.054 rows=6 loops=1)

  • Join Filter: ((mods.name)::text = "*VALUES*".column1)
  • Rows Removed by Join Filter: 195
26. 0.025 0.025 ↑ 1.0 36 1

Index Scan using m_modu_id_pk on m_modules mods (cost=0.14..13.78 rows=36 width=16) (actual time=0.003..0.025 rows=36 loops=1)

27. 0.000 0.000 ↑ 1.0 6 36

Materialize (cost=0.00..0.11 rows=6 width=32) (actual time=0.000..0.000 rows=6 loops=36)

28. 0.003 0.003 ↑ 1.0 6 1

Values Scan on "*VALUES*" (cost=0.00..0.08 rows=6 width=32) (actual time=0.001..0.003 rows=6 loops=1)

29. 11.034 11.034 ↓ 5.1 233 6

Index Scan using m_courmodu_mod_ix on m_course_modules cmod (cost=0.42..226.20 rows=46 width=40) (actual time=0.273..1.839 rows=233 loops=6)

  • Index Cond: (module = mods.id)
  • Filter: ((assessment_genre = 1) AND (visible = 1))
  • Rows Removed by Filter: 2899
30. 0.040 0.131 ↓ 1.1 355 1

Hash (cost=12.38..12.38 rows=338 width=38) (actual time=0.131..0.131 rows=355 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
31. 0.091 0.091 ↓ 1.1 355 1

Seq Scan on m_unisa_course ucrs (cost=0.00..12.38 rows=338 width=38) (actual time=0.008..0.091 rows=355 loops=1)

32. 1.397 1.397 ↑ 1.0 1 1,397

Index Scan using m_cour_id_pk on m_course mcrs (cost=0.28..0.45 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,397)

  • Index Cond: (id = cmod.course)
  • Filter: (visible = 1)
  • Rows Removed by Filter: 0
33. 1.270 1.270 ↑ 1.0 1 1,270

Index Scan using m_uniscouroffe_uni_ix on m_unisa_course_offering uco (cost=0.15..0.22 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=1,270)

  • Index Cond: (unisa_course_id = ucrs.id)
34. 1.270 1.270 ↑ 1.0 1 1,270

Index Scan using m_quiz_id_pk on m_quiz qz (cost=0.28..0.81 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=1,270)

  • Index Cond: (id = cmod.instance)
35. 1.270 1.270 ↑ 1.0 1 1,270

Index Scan using m_assi_id_pk on m_assign asgn (cost=0.28..0.52 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1,270)

  • Index Cond: (id = cmod.instance)
36. 1.270 1.270 ↑ 1.0 1 1,270

Index Scan using m_unisasse_cm__ix on m_unisa_assessment uass (cost=0.28..0.37 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=1,270)

  • Index Cond: (cm_id = cmod.id)
37. 0.000 0.000 ↓ 0.0 0 1,272

Index Scan using m_less_id_pk on m_lesson lsn (cost=0.14..0.18 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=1,272)

  • Index Cond: (id = cmod.instance)
38. 1.272 1.272 ↓ 0.0 0 1,272

Index Scan using m_data_id_pk on m_data dta (cost=0.28..0.56 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=1,272)

  • Index Cond: (id = cmod.instance)
39. 1.272 1.272 ↑ 1.0 1 1,272

Index Scan using m_foru_id_pk on m_forum frm (cost=0.29..1.63 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=1,272)

  • Index Cond: (id = cmod.instance)
40. 1.272 1.272 ↓ 0.0 0 1,272

Index Scan using m_scor_id_pk on m_scorm scrm (cost=0.14..0.19 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=1,272)

  • Index Cond: (id = cmod.instance)
41. 1.272 1.272 ↑ 1.0 2 1,272

Index Scan using m_grougrou_gro_ix on m_groupings_groups grgr (cost=0.28..0.39 rows=2 width=16) (actual time=0.001..0.001 rows=2 loops=1,272)

  • Index Cond: (groupingid = cmod.groupingid)
42. 2.152 2.152 ↓ 0.0 0 2,152

Index Only Scan using m_groumemb_usegro_uix on m_groups_members grmb (cost=0.29..0.50 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=2,152)

  • Index Cond: ((userid = 6664) AND (groupid = grgr.groupid))
  • Heap Fetches: 51
43. 0.110 0.110 ↑ 1.0 1 55

Index Scan using m_user_id_pk on m_user usr (cost=0.29..4.30 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=55)

  • Index Cond: (id = 6664)
44. 0.165 0.165 ↓ 4.0 8 55

Index Scan using m_grougrou_gro_ix on m_groupings_groups grgr2 (cost=0.28..0.56 rows=2 width=16) (actual time=0.001..0.003 rows=8 loops=55)

  • Index Cond: (groupingid = uco.grouping_id)
45. 0.421 0.421 ↓ 0.0 0 421

Index Only Scan using m_groumemb_usegro_uix on m_groups_members grmb2 (cost=0.29..0.50 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=421)

  • Index Cond: ((userid = 6664) AND (groupid = grgr2.groupid))
  • Heap Fetches: 69
46. 0.219 0.219 ↑ 1.0 1 73

Index Scan using m_assisubm_assusegrolat_ix on m_assign_submission asub (cost=0.42..1.87 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=73)

  • Index Cond: ((assignment = asgn.id) AND (userid = 6664))
47. 1.533 1.533 ↑ 1.6 18 73

Index Scan using m_file_use_ix on m_files fils (cost=0.42..172.04 rows=29 width=57) (actual time=0.004..0.021 rows=18 loops=73)

  • Index Cond: (userid = 6664)
  • Filter: ((filesize)::numeric > '0'::numeric)
  • Rows Removed by Filter: 18
48. 4.380 4.380 ↓ 4.2 151 73

Index Scan using m_plagturnfile_cm_ix on m_plagiarism_turnitin_files ptf (cost=0.29..3.62 rows=36 width=67) (actual time=0.003..0.060 rows=151 loops=73)

  • Index Cond: (cm = cmod.id)
  • Filter: ((statuscode)::text = 'success'::text)
  • Rows Removed by Filter: 17
49. 1.314 1.314 ↓ 1.3 39 73

Index Scan using m_graditem_cou_ix on m_grade_items grit (cost=0.29..4.15 rows=30 width=31) (actual time=0.002..0.018 rows=39 loops=73)

  • Index Cond: (courseid = ucrs.moodle_course_id)
50. 0.219 0.219 ↑ 1.0 1 73

Index Scan using m_gradgrad_useite_uix on m_grade_grades grd (cost=0.42..2.29 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=73)

  • Index Cond: ((userid = 6664) AND (itemid = grit.id))
51. 0.146 0.146 ↓ 7.0 7 73

Seq Scan on m_grade_letters grdl (cost=0.00..12.10 rows=1 width=532) (actual time=0.001..0.002 rows=7 loops=73)

  • Filter: ((contextid)::numeric = '1'::numeric)
52.          

SubPlan (forNested Loop Left Join)

53. 0.026 0.026 ↑ 1.0 1 13

Index Scan using m_quiz_id_pk on m_quiz (cost=0.28..4.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (id = cmod.instance)
  • Filter: (course = mcrs.id)
54. 0.112 0.112 ↑ 1.0 1 56

Index Scan using m_assi_id_pk on m_assign (cost=0.28..4.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=56)

  • Index Cond: (id = cmod.instance)
  • Filter: (course = mcrs.id)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using m_less_id_pk on m_lesson (cost=0.14..4.16 rows=1 width=8) (never executed)

  • Index Cond: (id = cmod.instance)
  • Filter: (course = mcrs.id)
56. 0.008 0.008 ↑ 1.0 1 4

Index Scan using m_foru_id_pk on m_forum (cost=0.29..4.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4)

  • Index Cond: (id = cmod.instance)
  • Filter: (course = mcrs.id)
57. 0.000 0.000 ↓ 0.0 0

Index Scan using m_scor_id_pk on m_scorm (cost=0.14..4.17 rows=1 width=8) (never executed)

  • Index Cond: (id = cmod.instance)
  • Filter: (course = mcrs.id)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using m_data_id_pk on m_data (cost=0.28..4.30 rows=1 width=8) (never executed)

  • Index Cond: (id = cmod.instance)
  • Filter: (course = mcrs.id)
59. 0.073 0.730 ↑ 1.0 1 73

Aggregate (cost=8.59..8.60 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=73)

60. 0.000 0.657 ↓ 0.0 0 73

Result (cost=0.28..8.58 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=73)

  • One-Time Filter: (cmod.course = ucrs.moodle_course_id)
61. 0.657 0.657 ↓ 0.0 0 73

Index Scan using m_deadexte_cm_staext_ix on m_deadline_extensions dle (cost=0.28..8.58 rows=1 width=8) (actual time=0.008..0.009 rows=0 loops=73)

  • Index Cond: (cm_id = cmod.id)
  • Filter: ((student_id = usr.id) AND ((status)::numeric = '2'::numeric))
  • Rows Removed by Filter: 14
62. 0.000 0.511 ↑ 1.0 1 73

Aggregate (cost=8.62..8.63 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=73)

63. 0.000 0.511 ↓ 0.0 0 73

Result (cost=0.28..8.62 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=73)

  • One-Time Filter: (cmod.course = ucrs.moodle_course_id)
64. 0.511 0.511 ↓ 0.0 0 73

Index Scan using m_deadexte_cm_staext_ix on m_deadline_extensions dle_1 (cost=0.28..8.62 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=73)

  • Index Cond: (cm_id = cmod.id)
  • Filter: (((student_id)::numeric = '0'::numeric) AND ((status)::numeric = '2'::numeric) AND ((ext_type)::numeric = '2'::numeric))
  • Rows Removed by Filter: 14
65. 0.006 0.174 ↑ 1.0 1 6

Aggregate (cost=49.91..49.92 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=6)

66. 0.168 0.168 ↑ 1.0 3 6

Index Scan using m_unisasse_catpcmcibass_ix on m_unisa_assessment uass2 (cost=0.28..49.90 rows=3 width=8) (actual time=0.010..0.028 rows=3 loops=6)

  • Index Cond: (cib_assessment_id = uass.cib_assessment_id)
67. 0.511 3.577 ↑ 1.0 1 511

Aggregate (cost=14.90..14.91 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=511)

68. 3.066 3.066 ↓ 2.0 2 511

Seq Scan on m_grade_letters grl2 (cost=0.00..14.90 rows=1 width=16) (actual time=0.005..0.006 rows=2 loops=511)

  • Filter: (((contextid)::numeric = '1'::numeric) AND (CASE WHEN ((grit.grademax - grit.grademin) = '0'::numeric) THEN '0'::numeric ELSE round((((grd.finalgrade - grit.grademin) * '100'::numeric) / (grit.grademax - grit.grademin)), 2) END >= lowerboundary))
  • Rows Removed by Filter: 5
Planning time : 19.891 ms
Execution time : 49.360 ms