explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jw3G

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 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.358 373.472 ↑ 6.3 214 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.280 373.114 ↑ 6.3 214 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.138 370.906 ↑ 6.3 214 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.025 370.482 ↑ 36.8 6 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. 8.577 370.418 ↑ 36.8 6 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.036 25.284 ↑ 36.8 6 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.014 25.248 ↑ 36.8 6 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.018 25.208 ↑ 36.8 6 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.053 25.164 ↑ 36.8 6 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.012 19.928 ↑ 36.8 6 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.009 19.890 ↑ 36.8 6 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.015 19.842 ↑ 36.8 6 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.017 19.792 ↑ 36.8 6 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.009 19.684 ↑ 36.8 6 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.017 19.636 ↑ 36.8 6 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.020 18.894 ↑ 36.8 6 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.008 18.848 ↑ 36.8 6 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.014 18.824 ↑ 25.0 1 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.008 18.800 ↑ 25.0 1 2

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

21. 18.778 18.778 ↓ 0.0 0 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.014 0.014 ↑ 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.012..0.014 rows=2 loops=1)

  • Index Cond: (userid = auser.id)
23. 0.004 0.010 ↑ 1.0 6 1

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.006 0.006 ↑ 1.0 6 1

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.016 0.016 ↑ 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.008 rows=6 loops=2)

  • Index Cond: (planid = aplan.id)
26. 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)
27. 0.236 0.725 ↑ 1.0 1,828 1

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.412 0.489 ↑ 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.091..0.489 rows=1,828 loops=1)

  • Recheck Cond: (fieldid = 2)
  • Heap Blocks: exact=88
29. 0.077 0.077 ↑ 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.077..0.077 rows=1,828 loops=1)

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

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.091 0.091 ↑ 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.007 rows=10 loops=13)

  • Index Cond: (aplan.userid = userid)
32. 0.016 0.035 ↑ 1.0 91 1

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.019 0.019 ↑ 1.0 91 1

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

  • Index Cond: ((userid = auser.id) AND (fieldid = 27))
35. 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))
36. 2.114 5.183 ↑ 1.0 13,916 1

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. 3.069 3.069 ↑ 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.008..3.069 rows=13,916 loops=1)

  • Filter: (type = 1)
38. 0.026 0.026 ↑ 1.0 1 13

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.026 0.026 ↑ 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.002..0.002 rows=1 loops=13)

  • Index Cond: (id = position_assignment.reportstoid)
  • Filter: (roleid = 9)
40. 8.097 336.557 ↑ 1.3 75,347 1

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. 17.599 328.460 ↑ 1.3 75,347 1

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. 39.315 310.861 ↑ 1.3 75,347 1

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. 65.140 271.546 ↑ 1.2 78,353 1

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. 67.738 206.406 ↓ 1.0 96,781 1

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. 27.032 138.668 ↓ 1.0 96,781 1

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. 43.936 100.619 ↑ 1.0 94,795 1

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. 26.015 26.015 ↑ 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..26.015 rows=94,795 loops=1)

  • Filter: (superceded = 0)
  • Rows Removed by Filter: 117582
48. 18.094 30.668 ↑ 1.0 94,795 1

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. 12.574 12.574 ↑ 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.009..12.574 rows=94,795 loops=1)

50. 2.285 11.017 ↓ 1.0 10,817 1

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. 3.974 8.732 ↓ 1.0 10,817 1

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. 1.043 1.043 ↑ 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.006..1.043 rows=10,817 loops=1)

53. 1.697 3.715 ↑ 1.0 10,624 1

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. 2.018 2.018 ↑ 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.009..2.018 rows=10,624 loops=1)

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

  • Index Cond: (id = manager_role_assignment.userid)
56. 0.286 0.286 ↓ 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.003..0.022 rows=32 loops=13)

  • Index Cond: (facetoface = facetoface.id)
57. 1.053 1.928 ↑ 1.0 6,060 1

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.875 0.875 ↑ 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.008..0.875 rows=6,060 loops=1)

Planning time : 5.951 ms
Execution time : 368.427 ms