explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C0er : Report 284

Settings
# exclusive inclusive rows x rows loops node
1. 121,913.699 259,562.574 ↑ 1.2 2,144,602 1

Sort (cost=7,978,371.80..7,984,921.81 rows=2,620,005 width=257) (actual time=227,066.899..259,562.574 rows=2,144,602 loops=1)

  • Sort Key: scorm.name, (max(mdl_scorm_scoes_track.id))
  • Sort Method: external merge Disk: 450,472kB
2. 3,744.512 137,648.875 ↑ 1.2 2,144,602 1

Merge Right Join (cost=6,919,156.09..7,468,789.36 rows=2,620,005 width=257) (actual time=123,148.736..137,648.875 rows=2,144,602 loops=1)

  • Merge Cond: (user_3.userid = auser.id)
3. 532.109 532.109 ↑ 1.0 125,274 1

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data user_3 (cost=0.43..141,322.74 rows=128,431 width=24) (actual time=0.046..532.109 rows=125,274 loops=1)

  • Index Cond: (fieldid = 3)
4. 1,938.829 133,372.254 ↑ 1.2 2,144,602 1

Materialize (cost=6,919,155.66..7,258,250.68 rows=2,620,005 width=241) (actual time=123,148.639..133,372.254 rows=2,144,602 loops=1)

5. 906.996 131,433.425 ↑ 1.2 2,144,602 1

Merge Left Join (cost=6,919,155.66..7,251,700.67 rows=2,620,005 width=241) (actual time=123,148.627..131,433.425 rows=2,144,602 loops=1)

  • Merge Cond: (auser.id = user_44.userid)
6. 869.665 129,843.580 ↑ 1.2 2,144,602 1

Merge Left Join (cost=6,919,155.23..7,101,700.41 rows=2,620,005 width=235) (actual time=123,148.612..129,843.580 rows=2,144,602 loops=1)

  • Merge Cond: (auser.id = user_43.userid)
7. 2,133.232 128,496.401 ↑ 1.2 2,144,602 1

Merge Left Join (cost=6,919,154.80..6,949,686.31 rows=2,620,005 width=229) (actual time=123,148.558..128,496.401 rows=2,144,602 loops=1)

  • Merge Cond: (auser.id = user_41.userid)
8. 8,320.118 125,292.199 ↑ 1.2 2,144,602 1

Sort (cost=6,835,607.23..6,842,157.25 rows=2,620,005 width=223) (actual time=122,244.416..125,292.199 rows=2,144,602 loops=1)

  • Sort Key: auser.id
  • Sort Method: external merge Disk: 368,032kB
9. 3,163.685 116,972.081 ↑ 1.2 2,144,602 1

Hash Join (cost=6,217,643.89..6,358,007.29 rows=2,620,005 width=223) (actual time=113,760.948..116,972.081 rows=2,144,602 loops=1)

  • Hash Cond: (cm.userid = u.id)
10. 140.875 263.242 ↑ 1.1 180,328 1

HashAggregate (cost=20,756.98..22,716.77 rows=195,979 width=8) (actual time=195.077..263.242 rows=180,328 loops=1)

11. 16.890 122.367 ↑ 1.1 180,398 1

Append (cost=0.43..20,267.04 rows=195,979 width=8) (actual time=0.155..122.367 rows=180,398 loops=1)

12. 55.455 55.455 ↑ 1.1 85,087 1

Index Scan using mdl_cohomemb_coh_ix on mdl_cohort_members cm (cost=0.43..9,091.50 rows=95,990 width=8) (actual time=0.154..55.455 rows=85,087 loops=1)

  • Index Cond: (cohortid = 39)
13. 50.022 50.022 ↑ 1.0 95,311 1

Index Scan using mdl_cohomemb_coh_ix on mdl_cohort_members cm_1 (cost=0.43..9,215.74 rows=99,989 width=8) (actual time=0.049..50.022 rows=95,311 loops=1)

  • Index Cond: (cohortid = 40)
14. 3,740.561 113,545.154 ↑ 1.2 2,243,133 1

Hash (cost=6,074,907.80..6,074,907.80 rows=2,727,209 width=239) (actual time=113,545.154..113,545.154 rows=2,243,133 loops=1)

  • Buckets: 32,768 Batches: 16 Memory Usage: 31,419kB
15. 1,879.678 109,804.593 ↑ 1.2 2,243,133 1

Hash Join (cost=3,313,954.30..6,074,907.80 rows=2,727,209 width=239) (actual time=50,519.067..109,804.593 rows=2,243,133 loops=1)

  • Hash Cond: (mdl_scorm_scoes_track.userid = u.id)
16. 2,076.787 107,739.100 ↑ 1.2 2,243,133 1

Hash Left Join (cost=3,302,422.45..6,008,831.77 rows=2,727,209 width=231) (actual time=50,332.950..107,739.100 rows=2,243,133 loops=1)

  • Hash Cond: (mdl_scorm_scoes_track.userid = auser.id)
17. 2,102.687 105,180.888 ↑ 1.2 2,243,133 1

Hash Left Join (cost=3,285,068.49..5,936,933.63 rows=2,727,209 width=180) (actual time=49,851.227..105,180.888 rows=2,243,133 loops=1)

  • Hash Cond: (mdl_scorm_scoes_track.scormid = scorm.id)
18. 2,066.770 103,049.040 ↑ 1.2 2,243,133 1

Merge Left Join (cost=3,275,114.89..5,882,662.88 rows=2,727,209 width=102) (actual time=49,822.012..103,049.040 rows=2,243,133 loops=1)

  • Merge Cond: ((mdl_scorm_scoes_track.userid = sco_scoreraw.userid) AND (mdl_scorm_scoes_track.scormid = sco_scoreraw.scormid) AND (mdl_scorm_scoes_track.scoid = sco_scoreraw.scoid) AND (mdl_scorm_scoes_track.attempt = sco_scoreraw.attempt))
19. 2,166.511 83,095.705 ↑ 1.2 2,243,105 1

Merge Left Join (cost=2,215,126.70..4,768,215.79 rows=2,727,209 width=87) (actual time=34,142.208..83,095.705 rows=2,243,105 loops=1)

  • Merge Cond: ((mdl_scorm_scoes_track.userid = sco_starttime.userid) AND (mdl_scorm_scoes_track.scormid = sco_starttime.scormid) AND (mdl_scorm_scoes_track.scoid = sco_starttime.scoid) AND (mdl_scorm_scoes_track.attempt = sco_starttime.attempt))
20. 2,532.815 62,445.500 ↑ 1.2 2,243,105 1

Merge Left Join (cost=1,101,950.06..3,594,726.92 rows=2,727,209 width=64) (actual time=18,056.843..62,445.500 rows=2,243,105 loops=1)

  • Merge Cond: ((mdl_scorm_scoes_track.userid = sco_status.userid) AND (mdl_scorm_scoes_track.scormid = sco_status.scormid) AND (mdl_scorm_scoes_track.scoid = sco_status.scoid) AND (mdl_scorm_scoes_track.attempt = sco_status.attempt))
21. 1,647.745 39,482.496 ↑ 1.2 2,243,063 1

Nested Loop (cost=0.71..2,433,696.04 rows=2,727,209 width=41) (actual time=0.303..39,482.496 rows=2,243,063 loops=1)

22. 9,333.831 37,834.751 ↑ 1.2 2,243,063 1

GroupAggregate (cost=0.56..2,372,329.68 rows=2,727,209 width=40) (actual time=0.241..37,834.751 rows=2,243,063 loops=1)

23. 28,500.920 28,500.920 ↑ 1.0 27,221,346 1

Index Scan using mdl_scorscoetrac_usescosco_uix on mdl_scorm_scoes_track (cost=0.56..2,004,156.46 rows=27,272,090 width=40) (actual time=0.183..28,500.920 rows=27,221,346 loops=1)

24. 0.000 0.000 ↑ 1.0 1 2,243,063

Materialize (cost=0.14..4.17 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=2,243,063)

25. 0.040 0.040 ↑ 1.0 1 1

Index Scan using mdl_userinfofiel_id_pk on mdl_user_info_field user_41_fielddefault (cost=0.14..4.16 rows=1 width=1) (actual time=0.034..0.040 rows=1 loops=1)

  • Index Cond: (id = 41)
26. 493.560 20,430.189 ↓ 1.0 2,174,162 1

Materialize (cost=1,101,949.35..1,112,552.49 rows=2,120,628 width=55) (actual time=18,056.529..20,430.189 rows=2,174,162 loops=1)

27. 7,769.205 19,936.629 ↓ 1.0 2,174,162 1

Sort (cost=1,101,949.35..1,107,250.92 rows=2,120,628 width=55) (actual time=18,056.519..19,936.629 rows=2,174,162 loops=1)

  • Sort Key: sco_status.userid, sco_status.scormid, sco_status.scoid, sco_status.attempt
  • Sort Method: external merge Disk: 109,624kB
28. 12,167.424 12,167.424 ↓ 1.0 2,174,162 1

Seq Scan on mdl_scorm_scoes_track sco_status (cost=0.00..827,338.12 rows=2,120,628 width=55) (actual time=0.171..12,167.424 rows=2,174,162 loops=1)

  • Filter: ((element)::text = ANY ('{cmi.core.lesson_status,cmi.completion_status}'::text[]))
  • Rows Removed by Filter: 25,047,184
29. 474.691 18,483.694 ↑ 1.0 2,140,516 1

Materialize (cost=1,113,176.64..1,124,190.02 rows=2,202,676 width=55) (actual time=16,085.352..18,483.694 rows=2,140,516 loops=1)

30. 7,787.432 18,009.003 ↑ 1.0 2,140,474 1

Sort (cost=1,113,176.64..1,118,683.33 rows=2,202,676 width=55) (actual time=16,085.326..18,009.003 rows=2,140,474 loops=1)

  • Sort Key: sco_starttime.userid, sco_starttime.scormid, sco_starttime.scoid, sco_starttime.attempt
  • Sort Method: external merge Disk: 121,352kB
31. 10,221.571 10,221.571 ↑ 1.0 2,140,474 1

Seq Scan on mdl_scorm_scoes_track sco_starttime (cost=0.00..827,338.12 rows=2,202,676 width=55) (actual time=0.163..10,221.571 rows=2,140,474 loops=1)

  • Filter: ((element)::text = 'x.start.time'::text)
  • Rows Removed by Filter: 25,080,872
32. 450.077 17,886.565 ↑ 1.0 1,777,601 1

Materialize (cost=1,059,988.19..1,069,050.46 rows=1,812,453 width=55) (actual time=15,679.792..17,886.565 rows=1,777,601 loops=1)

33. 6,212.698 17,436.488 ↑ 1.0 1,777,554 1

Sort (cost=1,059,988.19..1,064,519.33 rows=1,812,453 width=55) (actual time=15,679.782..17,436.488 rows=1,777,554 loops=1)

  • Sort Key: sco_scoreraw.userid, sco_scoreraw.scormid, sco_scoreraw.scoid, sco_scoreraw.attempt
  • Sort Method: external merge Disk: 86,864kB
34. 11,223.790 11,223.790 ↑ 1.0 1,777,554 1

Seq Scan on mdl_scorm_scoes_track sco_scoreraw (cost=0.00..827,338.12 rows=1,812,453 width=55) (actual time=0.176..11,223.790 rows=1,777,554 loops=1)

  • Filter: ((element)::text = ANY ('{cmi.core.score.raw,cmi.score.raw}'::text[]))
  • Rows Removed by Filter: 25,443,792
35. 2.531 29.161 ↓ 1.0 3,485 1

Hash (cost=9,910.19..9,910.19 rows=3,473 width=94) (actual time=29.161..29.161 rows=3,485 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 372kB
36. 4.911 26.630 ↓ 1.0 3,485 1

Nested Loop Left Join (cost=0.41..9,910.19 rows=3,473 width=94) (actual time=0.189..26.630 rows=3,485 loops=1)

37. 7.779 7.779 ↓ 1.0 3,485 1

Seq Scan on mdl_scorm scorm (cost=0.00..374.73 rows=3,473 width=43) (actual time=0.084..7.779 rows=3,485 loops=1)

38. 13.940 13.940 ↑ 1.0 1 3,485

Index Scan using mdl_cour_id_pk on mdl_course course (cost=0.41..2.74 rows=1 width=59) (actual time=0.004..0.004 rows=1 loops=3,485)

  • Index Cond: (id = scorm.course)
39. 119.027 481.425 ↓ 1.0 204,024 1

Hash (cost=14,803.98..14,803.98 rows=203,998 width=51) (actual time=481.425..481.425 rows=204,024 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 17,951kB
40. 362.398 362.398 ↓ 1.0 204,024 1

Seq Scan on mdl_user auser (cost=0.00..14,803.98 rows=203,998 width=51) (actual time=0.013..362.398 rows=204,024 loops=1)

41. 57.445 185.815 ↓ 1.0 204,024 1

Hash (cost=8,981.87..8,981.87 rows=203,998 width=8) (actual time=185.815..185.815 rows=204,024 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 7,970kB
42. 128.370 128.370 ↓ 1.0 204,024 1

Index Only Scan using mdl_user_id_pk on mdl_user u (cost=0.42..8,981.87 rows=203,998 width=8) (actual time=0.098..128.370 rows=204,024 loops=1)

  • Heap Fetches: 27,072
43. 223.173 1,070.970 ↓ 14.1 1,279,966 1

Sort (cost=83,547.56..83,773.77 rows=90,483 width=14) (actual time=904.117..1,070.970 rows=1,279,966 loops=1)

  • Sort Key: user_41.userid
  • Sort Method: quicksort Memory: 7,527kB
44. 847.797 847.797 ↑ 1.0 88,025 1

Seq Scan on mdl_user_info_data user_41 (cost=0.00..76,098.39 rows=90,483 width=14) (actual time=0.052..847.797 rows=88,025 loops=1)

  • Filter: (fieldid = 41)
  • Rows Removed by Filter: 3,340,334
45. 118.651 477.514 ↓ 18.5 1,108,499 1

Materialize (cost=0.43..134,044.50 rows=60,056 width=14) (actual time=0.035..477.514 rows=1,108,499 loops=1)

46. 358.863 358.863 ↓ 1.0 61,337 1

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data user_43 (cost=0.43..133,894.36 rows=60,056 width=14) (actual time=0.028..358.863 rows=61,337 loops=1)

  • Index Cond: (fieldid = 43)
47. 155.530 682.849 ↓ 18.8 1,077,663 1

Materialize (cost=0.43..132,529.02 rows=57,435 width=14) (actual time=0.010..682.849 rows=1,077,663 loops=1)

48. 527.319 527.319 ↓ 1.0 58,878 1

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data user_44 (cost=0.43..132,385.43 rows=57,435 width=14) (actual time=0.007..527.319 rows=58,878 loops=1)

  • Index Cond: (fieldid = 44)
Total runtime : 260,014.149 ms