explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Ir9

Settings
# exclusive inclusive rows x rows loops node
1. 20,631.328 48,929.297 ↑ 1.6 1,647,681 1

Sort (cost=2,283,410.78..2,290,195.48 rows=2,713,880 width=189) (actual time=43,347.390..48,929.297 rows=1,647,681 loops=1)

  • Sort Key: "*SELECT* 1".timecompleted, auser.username, "*SELECT* 1".id
  • Sort Method: external merge Disk: 305,080kB
2. 3,014.335 28,297.969 ↑ 1.6 1,647,681 1

Hash Join (cost=736,819.80..1,814,511.57 rows=2,713,880 width=189) (actual time=9,633.798..28,297.969 rows=1,647,681 loops=1)

  • Hash Cond: ("*SELECT* 1".courseid = course.id)
3. 1,418.317 25,250.915 ↑ 1.6 1,647,681 1

Hash Left Join (cost=726,588.46..1,705,902.07 rows=2,713,880 width=135) (actual time=9,601.025..25,250.915 rows=1,647,681 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = auser.id)
4. 182.179 22,459.027 ↑ 1.6 1,647,681 1

Append (cost=630,013.60..1,555,049.61 rows=2,713,880 width=56) (actual time=8,227.354..22,459.027 rows=1,647,681 loops=1)

5. 308.226 21,926.548 ↑ 1.7 1,475,359 1

Subquery Scan on *SELECT* 1 (cost=630,013.60..1,538,186.86 rows=2,467,867 width=56) (actual time=8,227.352..21,926.548 rows=1,475,359 loops=1)

6. 3,554.943 21,618.322 ↑ 1.7 1,475,359 1

Merge Right Join (cost=630,013.60..1,513,508.19 rows=2,467,867 width=48) (actual time=8,227.350..21,618.322 rows=1,475,359 loops=1)

  • Merge Cond: ((gg.userid = cc.userid) AND (gg.itemid = gi.id))
7. 8,034.208 8,034.208 ↑ 1.0 11,027,905 1

Index Scan using mdl_gradgrad_useite_uix on mdl_grade_grades gg (cost=0.56..784,891.77 rows=11,038,440 width=20) (actual time=0.022..8,034.208 rows=11,027,905 loops=1)

8. 395.523 10,029.171 ↑ 1.7 1,475,359 1

Materialize (cost=630,013.04..642,352.37 rows=2,467,867 width=52) (actual time=8,227.302..10,029.171 rows=1,475,359 loops=1)

9. 5,210.679 9,633.648 ↑ 1.7 1,475,359 1

Sort (cost=630,013.04..636,182.70 rows=2,467,867 width=52) (actual time=8,227.291..9,633.648 rows=1,475,359 loops=1)

  • Sort Key: cc.userid, gi.id
  • Sort Method: external merge Disk: 96,704kB
10. 434.180 4,422.969 ↑ 1.7 1,475,359 1

Merge Right Join (cost=2.69..307,736.82 rows=2,467,867 width=52) (actual time=0.081..4,422.969 rows=1,475,359 loops=1)

  • Merge Cond: (gi.courseid = cc.course)
11. 22.563 22.563 ↑ 1.0 5,937 1

Index Scan using mdl_graditem_cou_ix on mdl_grade_items gi (cost=0.29..5,863.57 rows=5,938 width=23) (actual time=0.034..22.563 rows=5,937 loops=1)

  • Filter: ((itemtype)::text = 'course'::text)
  • Rows Removed by Filter: 8,699
12. 512.194 3,966.226 ↑ 1.0 1,475,359 1

Materialize (cost=0.43..268,582.60 rows=1,490,940 width=37) (actual time=0.034..3,966.226 rows=1,475,359 loops=1)

13. 3,454.032 3,454.032 ↑ 1.0 1,475,359 1

Index Scan using mdl_courcomp_cou_ix on mdl_course_completions cc (cost=0.43..264,855.25 rows=1,490,940 width=37) (actual time=0.028..3,454.032 rows=1,475,359 loops=1)

  • Filter: (status > 10)
  • Rows Removed by Filter: 2,108,499
14. 31.312 350.300 ↑ 1.4 172,322 1

Subquery Scan on *SELECT* 2 (cost=3,372.22..16,862.75 rows=246,013 width=56) (actual time=31.732..350.300 rows=172,322 loops=1)

15. 155.494 318.988 ↑ 1.4 172,322 1

Merge Left Join (cost=3,372.22..14,402.62 rows=246,013 width=42) (actual time=31.730..318.988 rows=172,322 loops=1)

  • Merge Cond: (cch.courseid = gi_1.courseid)
16. 110.772 110.772 ↑ 1.0 172,322 1

Index Scan using mdl_courcomphist_cou_ix on mdl_course_completion_history cch (cost=0.42..5,710.35 rows=172,349 width=35) (actual time=0.038..110.772 rows=172,322 loops=1)

17. 23.729 52.722 ↓ 29.5 175,025 1

Sort (cost=3,364.16..3,379.01 rows=5,938 width=23) (actual time=31.633..52.722 rows=175,025 loops=1)

  • Sort Key: gi_1.courseid
  • Sort Method: quicksort Memory: 656kB
18. 28.993 28.993 ↑ 1.0 5,938 1

Seq Scan on mdl_grade_items gi_1 (cost=0.00..2,991.97 rows=5,938 width=23) (actual time=0.021..28.993 rows=5,938 loops=1)

  • Filter: ((itemtype)::text = 'course'::text)
  • Rows Removed by Filter: 8,700
19. 105.231 1,373.571 ↑ 1.0 205,514 1

Hash (cost=94,004.15..94,004.15 rows=205,657 width=87) (actual time=1,373.571..1,373.571 rows=205,514 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 24,650kB
20. 160.148 1,268.340 ↑ 1.0 205,514 1

Hash Right Join (cost=17,391.28..94,004.15 rows=205,657 width=87) (actual time=355.159..1,268.340 rows=205,514 loops=1)

  • Hash Cond: (user_42.userid = auser.id)
21. 753.140 753.140 ↓ 1.2 9,750 1

Seq Scan on mdl_user_info_data user_42 (cost=0.00..76,455.93 rows=7,847 width=14) (actual time=0.023..753.140 rows=9,750 loops=1)

  • Filter: (fieldid = 42)
  • Rows Removed by Filter: 3,441,799
22. 107.426 355.052 ↑ 1.0 205,514 1

Hash (cost=14,820.57..14,820.57 rows=205,657 width=81) (actual time=355.052..355.052 rows=205,514 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 24,524kB
23. 247.626 247.626 ↑ 1.0 205,514 1

Seq Scan on mdl_user auser (cost=0.00..14,820.57 rows=205,657 width=81) (actual time=0.013..247.626 rows=205,514 loops=1)

24. 3.349 32.719 ↑ 2.6 6,812 1

Hash (cost=10,006.71..10,006.71 rows=17,971 width=62) (actual time=32.719..32.719 rows=6,812 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 650kB
25. 29.370 29.370 ↑ 2.6 6,812 1

Seq Scan on mdl_course course (cost=0.00..10,006.71 rows=17,971 width=62) (actual time=0.016..29.370 rows=6,812 loops=1)

Total runtime : 49,128.918 ms