explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M3jf

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 83,070.681 ↑ 1.0 1 1

Limit (cost=8,176.55..11,568.87 rows=1 width=40) (actual time=83,070.681..83,070.681 rows=1 loops=1)

2. 16.484 83,070.679 ↑ 5.0 1 1

GroupAggregate (cost=8,176.55..25,138.16 rows=5 width=40) (actual time=83,070.679..83,070.679 rows=1 loops=1)

  • Group Key: c.id, g.id
3. 14,873.454 63,031.192 ↓ 337.6 1,688 1

Sort (cost=8,176.55..8,176.56 rows=5 width=40) (actual time=63,028.630..63,031.192 rows=1,688 loops=1)

  • Sort Key: c.id, g.id
  • Sort Method: external merge Disk: 392128kB
4. 469.404 48,157.738 ↓ 1,900,056.4 9,500,282 1

Nested Loop Left Join (cost=1,366.44..8,176.49 rows=5 width=40) (actual time=15.358..48,157.738 rows=9,500,282 loops=1)

  • Join Filter: (gm.groupid = g.id)
  • Rows Removed by Join Filter: 19496167
5. 879.610 28,687.850 ↓ 1,900,048.4 9,500,242 1

Nested Loop Left Join (cost=1,366.01..8,172.22 rows=5 width=32) (actual time=15.281..28,687.850 rows=9,500,242 loops=1)

6. 0.000 25,039.257 ↓ 395,569.0 395,569 1

Nested Loop (cost=1,365.72..8,171.37 rows=1 width=24) (actual time=15.276..25,039.257 rows=395,569 loops=1)

  • Join Filter: (c.id = e.courseid)
  • Rows Removed by Join Filter: 1656255
7. 488.731 21,169.401 ↓ 5,066.2 2,051,824 1

Nested Loop (cost=1,365.44..8,014.70 rows=405 width=40) (actual time=15.268..21,169.401 rows=2,051,824 loops=1)

8. 47.394 440.874 ↓ 519.3 348,962 1

Nested Loop (cost=1,365.01..7,540.38 rows=672 width=32) (actual time=15.101..440.874 rows=348,962 loops=1)

9. 10.488 52.616 ↓ 17.4 10,652 1

Hash Join (cost=1,364.59..4,768.61 rows=613 width=24) (actual time=15.073..52.616 rows=10,652 loops=1)

  • Hash Cond: (con.instanceid = c.id)
10. 27.597 30.516 ↓ 1.0 10,652 1

Bitmap Heap Scan on mdl_context con (cost=245.92..3,604.37 rows=10,516 width=16) (actual time=3.319..30.516 rows=10,652 loops=1)

  • Recheck Cond: (contextlevel = 50)
  • Heap Blocks: exact=1531
11. 2.919 2.919 ↓ 1.0 10,652 1

Bitmap Index Scan on mdl_cont_conins_uix (cost=0.00..243.29 rows=10,516 width=0) (actual time=2.919..2.919 rows=10,652 loops=1)

  • Index Cond: (contextlevel = 50)
12. 3.253 11.612 ↑ 1.0 10,652 1

Hash (cost=985.52..985.52 rows=10,652 width=8) (actual time=11.612..11.612 rows=10,652 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 545kB
13. 8.359 8.359 ↑ 1.0 10,652 1

Seq Scan on mdl_course c (cost=0.00..985.52 rows=10,652 width=8) (actual time=0.035..8.359 rows=10,652 loops=1)

14. 340.864 340.864 ↑ 1.9 33 10,652

Index Scan using mdl_roleassi_con_ix on mdl_role_assignments ra (cost=0.42..3.90 rows=62 width=16) (actual time=0.004..0.032 rows=33 loops=10,652)

  • Index Cond: (contextid = con.id)
  • Filter: (roleid = 13)
  • Rows Removed by Filter: 2
15. 20,239.796 20,239.796 ↓ 6.0 6 348,962

Index Scan using mdl_userenro_use_ix on mdl_user_enrolments ue (cost=0.42..0.70 rows=1 width=16) (actual time=0.012..0.058 rows=6 loops=348,962)

  • Index Cond: (userid = ra.userid)
  • Filter: (((to_timestamp((timestart)::double precision))::date >= (to_timestamp((timestart)::double precision))::date) AND ((to_timestamp((timestart)::double precision))::date <= (to_timestamp((GREATEST(timestart) (...)
16. 4,103.648 4,103.648 ↑ 1.0 1 2,051,824

Index Scan using mdl_enro_id_pk on mdl_enrol e (cost=0.29..0.37 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=2,051,824)

  • Index Cond: (id = ue.enrolid)
17. 2,768.983 2,768.983 ↓ 3.0 24 395,569

Index Scan using mdl_grou_cou_ix on mdl_groups g (cost=0.29..0.78 rows=8 width=16) (actual time=0.002..0.007 rows=24 loops=395,569)

  • Index Cond: (courseid = c.id)
18. 19,000.484 19,000.484 ↑ 1.5 2 9,500,242

Index Scan using mdl_groumemb_use_ix on mdl_groups_members gm (cost=0.42..0.82 rows=3 width=16) (actual time=0.001..0.002 rows=2 loops=9,500,242)

  • Index Cond: (userid = ue.userid)
19.          

SubPlan (for GroupAggregate)

20. 138.334 7,999.754 ↑ 1.0 1 1,687

Aggregate (cost=3,251.68..3,251.69 rows=1 width=16) (actual time=4.742..4.742 rows=1 loops=1,687)

21. 7,861.420 7,861.420 ↓ 8.1 455 1,687

Index Scan using mdl_logsstanlog_con_ix on mdl_logstore_standard_log (cost=0.44..3,251.40 rows=56 width=16) (actual time=0.066..4.660 rows=455 loops=1,687)

  • Index Cond: (contextid = con.id)
  • Filter: (((target)::text = 'course'::text) AND ((action)::text = 'viewed'::text))
  • Rows Removed by Filter: 16193
22. 173.761 12,023.249 ↑ 1.0 1 1,687

Aggregate (cost=140.58..140.59 rows=1 width=16) (actual time=7.127..7.127 rows=1 loops=1,687)

23. 7,321.580 11,849.488 ↓ 455.0 455 1,687

Bitmap Heap Scan on mdl_logstore_standard_log mdl_logstore_standard_log_1 (cost=136.55..140.57 rows=1 width=16) (actual time=3.067..7.024 rows=455 loops=1,687)

  • Recheck Cond: ((contextid = con.id) AND (courseid = c.id))
  • Filter: (((target)::text = 'course'::text) AND ((action)::text = 'viewed'::text))
  • Rows Removed by Filter: 16193
  • Heap Blocks: exact=4858560
24. 322.217 4,527.908 ↓ 0.0 0 1,687

BitmapAnd (cost=136.55..136.55 rows=1 width=0) (actual time=2.684..2.684 rows=0 loops=1,687)

25. 1,690.374 1,690.374 ↓ 12.3 16,648 1,687

Bitmap Index Scan on mdl_logsstanlog_con_ix (cost=0.00..26.62 rows=1,357 width=0) (actual time=1.002..1.002 rows=16,648 loops=1,687)

  • Index Cond: (contextid = con.id)
26. 2,515.317 2,515.317 ↓ 6.2 23,956 1,687

Bitmap Index Scan on mdl_logsstanlog_couanotim_ix (cost=0.00..109.69 rows=3,883 width=0) (actual time=1.491..1.491 rows=23,956 loops=1,687)

  • Index Cond: (courseid = c.id)
Planning time : 15.600 ms
Execution time : 83,115.406 ms