explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hNfa

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 34,663.952 ↓ 0.0 0 1

Sort (cost=1,640,584.39..1,640,584.81 rows=169 width=189) (actual time=34,663.952..34,663.952 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".id
  • Sort Method: quicksort Memory: 25kB
2. 0.002 34,663.892 ↓ 0.0 0 1

Nested Loop Left Join (cost=647,405.72..1,640,578.14 rows=169 width=189) (actual time=34,663.892..34,663.892 rows=0 loops=1)

3. 0.001 34,663.890 ↓ 0.0 0 1

Nested Loop (cost=647,405.29..1,640,406.66 rows=169 width=183) (actual time=34,663.890..34,663.890 rows=0 loops=1)

4. 4,664.869 34,663.889 ↓ 0.0 0 1

Hash Left Join (cost=647,404.88..1,640,283.90 rows=271 width=129) (actual time=34,663.889..34,663.889 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = auser.id)
  • Filter: ((CASE WHEN (auser.maildisplay <> 1) THEN '-'::character varying ELSE auser.email END)::text ~~* '%@peopleplus.co.uk%'::text)
  • Rows Removed by Filter: 1,647,674
5. 244.614 29,465.101 ↑ 1.6 1,647,674 1

Append (cost=630,013.60..1,555,045.61 rows=2,713,880 width=56) (actual time=10,684.534..29,465.101 rows=1,647,674 loops=1)

6. 345.571 28,590.602 ↑ 1.7 1,475,352 1

Subquery Scan on *SELECT* 1 (cost=630,013.60..1,538,182.86 rows=2,467,867 width=56) (actual time=10,684.534..28,590.602 rows=1,475,352 loops=1)

7. 4,157.598 28,245.031 ↑ 1.7 1,475,352 1

Merge Right Join (cost=630,013.60..1,513,504.19 rows=2,467,867 width=48) (actual time=10,684.533..28,245.031 rows=1,475,352 loops=1)

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

Index Scan using mdl_gradgrad_useite_uix on mdl_grade_grades gg (cost=0.56..784,887.77 rows=11,038,440 width=20) (actual time=0.010..11,187.598 rows=11,027,884 loops=1)

9. 439.179 12,899.835 ↑ 1.7 1,475,352 1

Materialize (cost=630,013.04..642,352.37 rows=2,467,867 width=52) (actual time=10,684.484..12,899.835 rows=1,475,352 loops=1)

10. 6,354.524 12,460.656 ↑ 1.7 1,475,352 1

Sort (cost=630,013.04..636,182.70 rows=2,467,867 width=52) (actual time=10,684.474..12,460.656 rows=1,475,352 loops=1)

  • Sort Key: cc.userid, gi.id
  • Sort Method: external merge Disk: 96,704kB
11. 480.668 6,106.132 ↑ 1.7 1,475,352 1

Merge Right Join (cost=2.69..307,736.82 rows=2,467,867 width=52) (actual time=0.100..6,106.132 rows=1,475,352 loops=1)

  • Merge Cond: (gi.courseid = cc.course)
12. 64.379 64.379 ↑ 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.011..64.379 rows=5,937 loops=1)

  • Filter: ((itemtype)::text = 'course'::text)
  • Rows Removed by Filter: 8,699
13. 604.582 5,561.085 ↑ 1.0 1,475,352 1

Materialize (cost=0.43..268,582.60 rows=1,490,940 width=37) (actual time=0.025..5,561.085 rows=1,475,352 loops=1)

14. 4,956.503 4,956.503 ↑ 1.0 1,475,352 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.018..4,956.503 rows=1,475,352 loops=1)

  • Filter: (status > 10)
  • Rows Removed by Filter: 2,108,478
15. 54.562 629.885 ↑ 1.4 172,322 1

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

16. 252.447 575.323 ↑ 1.4 172,322 1

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

  • Merge Cond: (cch.courseid = gi_1.courseid)
17. 219.097 219.097 ↑ 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.087..219.097 rows=172,322 loops=1)

18. 46.505 103.779 ↓ 29.5 175,025 1

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

  • Sort Key: gi_1.courseid
  • Sort Method: quicksort Memory: 656kB
19. 57.274 57.274 ↑ 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.035..57.274 rows=5,938 loops=1)

  • Filter: ((itemtype)::text = 'course'::text)
  • Rows Removed by Filter: 8,700
20. 157.947 533.919 ↑ 1.0 205,514 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 24,925kB
21. 375.972 375.972 ↑ 1.0 205,514 1

Seq Scan on mdl_user auser (cost=0.00..14,820.57 rows=205,657 width=83) (actual time=0.021..375.972 rows=205,514 loops=1)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using mdl_cour_id_pk on mdl_course course (cost=0.41..0.44 rows=1 width=62) (never executed)

  • Index Cond: (id = "*SELECT* 1".courseid)
  • Filter: ((coursetype = 1) OR (coursetype = 0))
23. 0.000 0.000 ↓ 0.0 0

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data user_42 (cost=0.43..0.99 rows=1 width=14) (never executed)

  • Index Cond: ((userid = auser.id) AND (fieldid = 42))
Total runtime : 34,718.178 ms