explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mzYz

Settings
# exclusive inclusive rows x rows loops node
1. 39.403 1,920.165 ↓ 913.3 11,873 1

Sort (cost=136,804.98..136,805.01 rows=13 width=355) (actual time=1,914.306..1,920.165 rows=11,873 loops=1)

  • Sort Key: base.id
  • Sort Method: external merge Disk: 6056kB
2. 20.877 1,880.762 ↓ 913.3 11,873 1

Nested Loop Left Join (cost=58,701.30..136,804.74 rows=13 width=355) (actual time=882.575..1,880.762 rows=11,873 loops=1)

3. 4.420 1,836.139 ↓ 913.3 11,873 1

Nested Loop Left Join (cost=58,701.02..136,797.95 rows=13 width=348) (actual time=882.563..1,836.139 rows=11,873 loops=1)

4. 7.903 1,796.100 ↓ 913.3 11,873 1

Nested Loop Left Join (cost=58,700.59..136,781.63 rows=13 width=340) (actual time=882.553..1,796.100 rows=11,873 loops=1)

5. 9.272 1,752.578 ↓ 913.3 11,873 1

Hash Join (cost=58,700.17..136,765.31 rows=13 width=332) (actual time=882.542..1,752.578 rows=11,873 loops=1)

  • Hash Cond: (facetoface.course = course.id)
6. 14.017 1,740.414 ↓ 1.3 12,431 1

Nested Loop Left Join (cost=58,606.48..136,635.80 rows=9,523 width=285) (actual time=879.633..1,740.414 rows=12,431 loops=1)

7. 4.959 1,701.535 ↓ 1.3 12,431 1

Nested Loop Left Join (cost=58,606.06..124,682.53 rows=9,523 width=277) (actual time=879.625..1,701.535 rows=12,431 loops=1)

8. 6.759 1,659.283 ↓ 1.3 12,431 1

Nested Loop Left Join (cost=58,605.63..112,729.25 rows=9,523 width=269) (actual time=879.621..1,659.283 rows=12,431 loops=1)

9. 9.133 1,615.231 ↓ 1.3 12,431 1

Hash Join (cost=58,605.21..100,775.98 rows=9,523 width=261) (actual time=879.616..1,615.231 rows=12,431 loops=1)

  • Hash Cond: (sessions.facetoface = facetoface.id)
10. 6.291 1,605.662 ↓ 1.3 12,431 1

Nested Loop Left Join (cost=58,537.39..100,577.22 rows=9,523 width=222) (actual time=879.161..1,605.662 rows=12,431 loops=1)

11. 5.547 1,549.647 ↓ 1.3 12,431 1

Nested Loop Left Join (cost=58,536.97..88,623.95 rows=9,523 width=214) (actual time=879.144..1,549.647 rows=12,431 loops=1)

12. 24.909 1,146.308 ↓ 1.3 12,431 1

Hash Join (cost=58,536.55..76,670.68 rows=9,523 width=206) (actual time=879.076..1,146.308 rows=12,431 loops=1)

  • Hash Cond: (sessions.id = sessiondate.sessionid)
13. 54.882 1,117.849 ↓ 1.2 118,542 1

Hash Join (cost=58,040.83..75,212.84 rows=99,073 width=186) (actual time=801.640..1,117.849 rows=118,542 loops=1)

  • Hash Cond: (base.sessionid = sessions.id)
14. 98.920 1,053.364 ↓ 1.2 118,542 1

Hash Left Join (cost=56,605.62..72,415.37 rows=99,073 width=138) (actual time=791.938..1,053.364 rows=118,542 loops=1)

  • Hash Cond: (base.userid = auser.id)
15. 93.904 575.716 ↓ 1.2 118,542 1

Hash Join (cost=28,695.98..41,071.48 rows=99,073 width=40) (actual time=413.012..575.716 rows=118,542 loops=1)

  • Hash Cond: (status.signupid = base.id)
16. 69.257 69.257 ↓ 1.2 118,542 1

Seq Scan on mdl_facetoface_signups_status status (cost=0.00..9,073.25 rows=99,073 width=16) (actual time=0.059..69.257 rows=118,542 loops=1)

  • Filter: ((superceded = 0) AND ((statuscode = 100) OR (statuscode = 90) OR (statuscode = 80) OR (statuscode = 70)))
  • Rows Removed by Filter: 166358
17. 33.666 412.555 ↑ 1.0 142,133 1

Hash (cost=25,947.31..25,947.31 rows=142,133 width=32) (actual time=412.555..412.555 rows=142,133 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2741kB
18. 40.570 378.889 ↑ 1.0 142,133 1

Merge Left Join (cost=17,725.95..25,947.31 rows=142,133 width=32) (actual time=269.348..378.889 rows=142,133 loops=1)

  • Merge Cond: (base.sessionid = attendees.sessionid)
19. 57.425 57.425 ↑ 1.0 142,133 1

Index Scan using mdl_facesign_ses_ix on mdl_facetoface_signups base (cost=0.42..5,734.45 rows=142,133 width=24) (actual time=0.029..57.425 rows=142,133 loops=1)

20. 16.509 280.894 ↓ 11.8 141,406 1

Sort (cost=17,725.53..17,755.61 rows=12,031 width=16) (actual time=269.309..280.894 rows=141,406 loops=1)

  • Sort Key: attendees.sessionid
  • Sort Method: quicksort Memory: 1560kB
21. 1.596 264.385 ↓ 1.4 16,894 1

Subquery Scan on attendees (cost=16,669.54..16,910.16 rows=12,031 width=16) (actual time=257.897..264.385 rows=16,894 loops=1)

22. 40.085 262.789 ↓ 1.4 16,894 1

HashAggregate (cost=16,669.54..16,789.85 rows=12,031 width=16) (actual time=257.896..262.789 rows=16,894 loops=1)

  • Group Key: su.sessionid
23. 88.437 222.704 ↑ 1.1 118,587 1

Hash Join (cost=5,358.99..16,024.46 rows=129,016 width=16) (actual time=66.415..222.704 rows=118,587 loops=1)

  • Hash Cond: (ss.signupid = su.id)
24. 68.478 68.478 ↑ 1.1 118,587 1

Seq Scan on mdl_facetoface_signups_status ss (cost=0.00..6,936.50 rows=129,016 width=16) (actual time=0.016..68.478 rows=118,587 loops=1)

  • Filter: ((statuscode >= 50) AND (superceded = 0))
  • Rows Removed by Filter: 166313
25. 31.081 65.789 ↑ 1.0 142,133 1

Hash (cost=2,887.33..2,887.33 rows=142,133 width=16) (actual time=65.789..65.789 rows=142,133 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2689kB
26. 34.708 34.708 ↑ 1.0 142,133 1

Seq Scan on mdl_facetoface_signups su (cost=0.00..2,887.33 rows=142,133 width=16) (actual time=0.008..34.708 rows=142,133 loops=1)

27. 14.579 378.728 ↑ 1.0 31,381 1

Hash (cost=26,995.09..26,995.09 rows=31,404 width=106) (actual time=378.728..378.728 rows=31,381 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2576kB
28. 21.776 364.149 ↑ 1.0 31,381 1

Hash Right Join (cost=15,183.52..26,995.09 rows=31,404 width=106) (actual time=275.284..364.149 rows=31,381 loops=1)

  • Hash Cond: (auser_cf_21.userid = auser.id)
29. 67.286 67.286 ↓ 1.0 21,214 1

Seq Scan on mdl_user_info_data auser_cf_21 (cost=0.00..10,821.65 rows=21,158 width=16) (actual time=0.085..67.286 rows=21,214 loops=1)

  • Filter: (fieldid = 21)
  • Rows Removed by Filter: 501638
30. 15.455 275.087 ↑ 1.0 31,381 1

Hash (cost=14,299.97..14,299.97 rows=31,404 width=98) (actual time=275.087..275.087 rows=31,381 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2451kB
31. 24.844 259.632 ↑ 1.0 31,381 1

Hash Right Join (cost=2,521.59..14,299.97 rows=31,404 width=98) (actual time=51.145..259.632 rows=31,381 loops=1)

  • Hash Cond: (auser_cf_20.userid = auser.id)
32. 184.069 184.069 ↓ 1.0 21,199 1

Seq Scan on mdl_user_info_data auser_cf_20 (cost=0.00..10,821.65 rows=21,071 width=16) (actual time=0.325..184.069 rows=21,199 loops=1)

  • Filter: (fieldid = 20)
  • Rows Removed by Filter: 501653
33. 15.934 50.719 ↑ 1.0 31,381 1

Hash (cost=1,668.04..1,668.04 rows=31,404 width=90) (actual time=50.719..50.719 rows=31,381 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2268kB
34. 34.785 34.785 ↑ 1.0 31,381 1

Seq Scan on mdl_user auser (cost=0.00..1,668.04 rows=31,404 width=90) (actual time=1.687..34.785 rows=31,381 loops=1)

35. 4.248 9.603 ↑ 1.0 18,612 1

Hash (cost=1,202.57..1,202.57 rows=18,612 width=48) (actual time=9.603..9.603 rows=18,612 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1274kB
36. 2.343 5.355 ↑ 1.0 18,612 1

Nested Loop (cost=0.00..1,202.57 rows=18,612 width=48) (actual time=0.010..5.355 rows=18,612 loops=1)

37. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on mdl_user_info_field auser_cf_20_fielddefault (cost=0.00..1.32 rows=1 width=32) (actual time=0.004..0.011 rows=1 loops=1)

  • Filter: (id = 20)
  • Rows Removed by Filter: 25
38. 3.001 3.001 ↑ 1.0 18,612 1

Seq Scan on mdl_facetoface_sessions sessions (cost=0.00..1,015.12 rows=18,612 width=16) (actual time=0.001..3.001 rows=18,612 loops=1)

39. 0.382 3.550 ↓ 1.0 1,797 1

Hash (cost=473.35..473.35 rows=1,789 width=44) (actual time=3.550..3.550 rows=1,797 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 147kB
40. 3.168 3.168 ↓ 1.0 1,797 1

Seq Scan on mdl_facetoface_sessions_dates sessiondate (cost=0.00..473.35 rows=1,789 width=44) (actual time=0.207..3.168 rows=1,797 loops=1)

  • Filter: ((timestart <> 0) AND (timestart >= 1549324800))
  • Rows Removed by Filter: 16960
41. 397.792 397.792 ↑ 1.0 1 12,431

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data auser_cf_34 (cost=0.42..1.25 rows=1 width=16) (actual time=0.032..0.032 rows=1 loops=12,431)

  • Index Cond: ((userid = auser.id) AND (fieldid = 34))
42. 49.724 49.724 ↑ 1.0 1 12,431

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data auser_cf_19 (cost=0.42..1.25 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=12,431)

  • Index Cond: ((userid = auser.id) AND (fieldid = 19))
43. 0.170 0.436 ↑ 1.0 703 1

Hash (cost=59.03..59.03 rows=703 width=55) (actual time=0.436..0.436 rows=703 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
44. 0.266 0.266 ↑ 1.0 703 1

Seq Scan on mdl_facetoface facetoface (cost=0.00..59.03 rows=703 width=55) (actual time=0.003..0.266 rows=703 loops=1)

45. 37.293 37.293 ↑ 1.0 1 12,431

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data auser_cf_35 (cost=0.42..1.25 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=12,431)

  • Index Cond: ((userid = auser.id) AND (fieldid = 35))
46. 37.293 37.293 ↑ 1.0 1 12,431

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data auser_cf_18 (cost=0.42..1.25 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=12,431)

  • Index Cond: ((userid = auser.id) AND (fieldid = 18))
47. 24.862 24.862 ↑ 1.0 1 12,431

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data auser_cf_22 (cost=0.42..1.25 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=12,431)

  • Index Cond: ((userid = auser.id) AND (fieldid = 22))
48. 0.136 2.892 ↓ 353.0 353 1

Hash (cost=93.68..93.68 rows=1 width=63) (actual time=2.892..2.892 rows=353 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
49. 0.000 2.756 ↓ 353.0 353 1

Nested Loop (cost=4.83..93.68 rows=1 width=63) (actual time=0.443..2.756 rows=353 loops=1)

50. 0.141 0.656 ↓ 11.0 353 1

Nested Loop (cost=4.54..38.13 rows=32 width=47) (actual time=0.415..0.656 rows=353 loops=1)

51. 0.032 0.032 ↑ 1.0 1 1

Seq Scan on mdl_course_categories course_category (cost=0.00..1.35 rows=1 width=8) (actual time=0.028..0.032 rows=1 loops=1)

  • Filter: ((path)::text = '/90'::text)
  • Rows Removed by Filter: 27
52. 0.114 0.483 ↓ 10.4 353 1

Bitmap Heap Scan on mdl_course course (cost=4.54..36.44 rows=34 width=55) (actual time=0.382..0.483 rows=353 loops=1)

  • Recheck Cond: (category = course_category.id)
  • Heap Blocks: exact=20
53. 0.369 0.369 ↓ 10.4 353 1

Bitmap Index Scan on mdl_cour_cat_ix (cost=0.00..4.53 rows=34 width=0) (actual time=0.369..0.369 rows=353 loops=1)

  • Index Cond: (category = course_category.id)
54. 2.118 2.118 ↑ 1.0 1 353

Index Scan using mdl_cont_ins_ix on mdl_context ctx (cost=0.29..1.73 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=353)

  • Index Cond: (instanceid = course.id)
  • Filter: (contextlevel = 50)
  • Rows Removed by Filter: 2
55. 35.619 35.619 ↑ 1.0 1 11,873

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data auser_cf_29 (cost=0.42..1.25 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=11,873)

  • Index Cond: ((userid = auser.id) AND (fieldid = 29))
56. 35.619 35.619 ↑ 1.0 1 11,873

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data auser_cf_30 (cost=0.42..1.25 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=11,873)

  • Index Cond: ((userid = auser.id) AND (fieldid = 30))
57. 23.746 23.746 ↑ 1.0 1 11,873

Index Scan using mdl_faceroom_id_pk on mdl_facetoface_room room (cost=0.28..0.49 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=11,873)

  • Index Cond: (sessiondate.roomid = id)