explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H6vc

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 15,282.335 ↓ 1.4 11 1

Unique (cost=184,475.85..184,476.11 rows=8 width=232) (actual time=15,282.328..15,282.335 rows=11 loops=1)

2. 0.043 15,282.327 ↓ 1.4 11 1

Sort (cost=184,475.85..184,475.87 rows=8 width=232) (actual time=15,282.327..15,282.327 rows=11 loops=1)

  • Sort Key: result.course_name, result.teacher_name, result.code_pole, result.course_code, (count(result.u_id)), result.session, result.affectation_type, student_group.group_name, result.pole_name, result.rc_marks_publi, result.rc_id, result.cr_id
  • Sort Method: quicksort Memory: 27kB
3. 0.426 15,282.284 ↓ 1.4 11 1

HashAggregate (cost=184,475.65..184,475.73 rows=8 width=232) (actual time=15,282.278..15,282.284 rows=11 loops=1)

  • Group Key: result.course_name, result.teacher_name, result.code_pole, result.course_code, result.session, result.affectation_type, student_group.group_name, result.pole_name, result.rc_marks_publi, result.rc_id, result.cr_id
4. 0.122 15,281.858 ↓ 37.1 297 1

Hash Join (cost=184,471.73..184,475.41 rows=8 width=232) (actual time=15,281.734..15,281.858 rows=297 loops=1)

  • Hash Cond: (student_group.student_group_id = result.student_group_id)
5. 0.034 0.034 ↓ 1.0 117 1

Seq Scan on t_student_group student_group (cost=0.00..3.16 rows=116 width=51) (actual time=0.010..0.034 rows=117 loops=1)

6. 0.182 15,281.702 ↓ 37.1 297 1

Hash (cost=184,471.63..184,471.63 rows=8 width=189) (actual time=15,281.702..15,281.702 rows=297 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
7. 0.058 15,281.520 ↓ 37.1 297 1

Subquery Scan on result (cost=184,471.43..184,471.63 rows=8 width=189) (actual time=15,281.338..15,281.520 rows=297 loops=1)

8. 3.307 15,281.462 ↓ 37.1 297 1

HashAggregate (cost=184,471.43..184,471.55 rows=8 width=102) (actual time=15,281.338..15,281.462 rows=297 loops=1)

  • Group Key: CASE WHEN (t_departement.dept_code IS NOT NULL) THEN t_departement.dept_code ELSE '(Aucun)'::character varying END, courses.cr_code, course.course_name, t.u_id, CASE WHEN tt.tc_is_temp THEN '(Aucun)'::text ELSE (((COALESCE(tt.tc_civility, 'M.'::character varying))::text || ' '::text) || (tt.tc_name)::text) END, r.session, 'Classe'::text, CASE WHEN (t_departement.dept_code IS NOT NULL) THEN t_departement.dept_name ELSE '(Aucun)'::character varying END, rc.rc_marks_publi, rc.rc_id, rc.cr_id, rc.student_group_id
9. 3.709 15,278.155 ↓ 37.1 297 1

Nested Loop Left Join (cost=61,230.14..184,471.19 rows=8 width=102) (actual time=314.335..15,278.155 rows=297 loops=1)

10. 79.977 883.014 ↓ 37.1 297 1

Nested Loop Left Join (cost=61,229.28..91,766.38 rows=8 width=102) (actual time=266.211..883.014 rows=297 loops=1)

  • Join Filter: (rc.rc_id = webexam.rc_id)
  • Rows Removed by Join Filter: 653994
11. 1.947 539.598 ↓ 37.1 297 1

Nested Loop (cost=10.80..12,694.53 rows=8 width=102) (actual time=44.449..539.598 rows=297 loops=1)

12. 0.453 531.956 ↓ 20.9 335 1

Nested Loop (cost=10.37..12,684.75 rows=16 width=102) (actual time=44.410..531.956 rows=335 loops=1)

13. 0.098 23.545 ↓ 11.0 11 1

Nested Loop Left Join (cost=9.94..1,104.23 rows=1 width=98) (actual time=5.213..23.545 rows=11 loops=1)

  • Join Filter: (courses.dept_id = t_departement.dept_id)
  • Rows Removed by Join Filter: 374
14. 0.060 23.381 ↓ 11.0 11 1

Nested Loop Left Join (cost=9.94..1,102.56 rows=1 width=73) (actual time=5.200..23.381 rows=11 loops=1)

15. 1.227 23.233 ↓ 11.0 11 1

Nested Loop (cost=9.66..1,102.23 rows=1 width=65) (actual time=5.192..23.233 rows=11 loops=1)

  • Join Filter: (rc.student_group_id = r.student_group_id)
  • Rows Removed by Join Filter: 1147
16. 0.963 10.426 ↓ 1,158.0 1,158 1

Nested Loop (cost=9.66..1,100.16 rows=1 width=57) (actual time=3.551..10.426 rows=1,158 loops=1)

17. 2.797 7.459 ↓ 501.0 501 1

Hash Join (cost=9.37..1,099.54 rows=1 width=35) (actual time=3.536..7.459 rows=501 loops=1)

  • Hash Cond: (courses.course_id = course.course_id)
18. 4.526 4.526 ↓ 1.0 34,776 1

Seq Scan on t_courses courses (cost=0.00..962.21 rows=34,121 width=17) (actual time=0.007..4.526 rows=34,776 loops=1)

19. 0.073 0.136 ↑ 1.0 283 1

Hash (cost=5.83..5.83 rows=283 width=26) (actual time=0.136..0.136 rows=283 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
20. 0.063 0.063 ↑ 1.0 283 1

Seq Scan on t_course course (cost=0.00..5.83 rows=283 width=26) (actual time=0.008..0.063 rows=283 loops=1)

21. 2.004 2.004 ↑ 2.0 2 501

Index Scan using r_courses_crid_index on r_courses rc (cost=0.29..0.58 rows=4 width=26) (actual time=0.003..0.004 rows=2 loops=501)

  • Index Cond: (cr_id = courses.cr_id)
22. 11.580 11.580 ↑ 1.0 1 1,158

Seq Scan on r_promo_attached_to_student_group r (cost=0.00..2.06 rows=1 width=12) (actual time=0.003..0.010 rows=1 loops=1,158)

  • Filter: ((y_id = 2018) AND (p_uid = 3358) AND (tri_id = 41) AND ((session)::text = 'OCTOBRE'::text))
  • Rows Removed by Filter: 52
23. 0.088 0.088 ↑ 1.0 1 11

Index Scan using t_teacher_pkey on t_teacher tt (cost=0.28..0.31 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=11)

  • Index Cond: (rc.u_id = u_id)
24. 0.066 0.066 ↓ 1.1 34 11

Seq Scan on t_departement (cost=0.00..1.30 rows=30 width=33) (actual time=0.003..0.006 rows=34 loops=11)

25. 507.958 507.958 ↑ 1.3 30 11

Index Scan using r_courses_period_pkey on r_courses_period rcp (cost=0.43..11,580.14 rows=38 width=8) (actual time=39.176..46.178 rows=30 loops=11)

  • Index Cond: (rc_id = rc.rc_id)
  • Filter: rcp_enabled
26. 5.695 5.695 ↑ 1.0 1 335

Index Scan using t_period_pkey on t_period t (cost=0.42..0.60 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=335)

  • Index Cond: (per_id = rcp.per_id)
  • Filter: per_register
  • Rows Removed by Filter: 0
27. 42.475 263.439 ↑ 2.6 2,202 297

Materialize (cost=61,218.49..78,404.56 rows=5,679 width=4) (actual time=0.725..0.887 rows=2,202 loops=297)

28. 2.891 220.964 ↑ 2.6 2,202 1

Hash Join (cost=61,218.49..78,376.17 rows=5,679 width=4) (actual time=214.970..220.964 rows=2,202 loops=1)

  • Hash Cond: (webexam.per_id = perexam.per_id)
29. 10.377 10.377 ↑ 5.1 2,320 1

Index Scan using ecr_rcp_exam_web on r_courses_period webexam (cost=0.43..16,969.49 rows=11,718 width=8) (actual time=6.978..10.377 rows=2,320 loops=1)

  • Index Cond: (rcp_exam_web IS NOT NULL)
  • Filter: (rcp_enabled AND ((rcp_exam_web)::text <> ''::text))
  • Rows Removed by Filter: 9153
30. 54.320 207.696 ↑ 1.8 254,031 1

Hash (cost=55,399.40..55,399.40 rows=465,493 width=4) (actual time=207.696..207.696 rows=254,031 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 8931kB
31. 153.376 153.376 ↑ 1.8 254,031 1

Seq Scan on t_period perexam (cost=0.00..55,399.40 rows=465,493 width=4) (actual time=0.013..153.376 rows=254,031 loops=1)

  • Filter: per_register
  • Rows Removed by Filter: 268597
32. 0.594 14,391.432 ↓ 0.0 0 297

Nested Loop (cost=0.85..11,588.08 rows=2 width=4) (actual time=48.456..48.456 rows=0 loops=297)

33. 14,390.838 14,390.838 ↓ 0.0 0 297

Index Scan using r_courses_period_pkey on r_courses_period webmarks (cost=0.43..11,580.14 rows=4 width=8) (actual time=48.454..48.454 rows=0 loops=297)

  • Index Cond: (rc.rc_id = rc_id)
  • Filter: ((rcp_marks_web IS NOT NULL) AND rcp_enabled AND (rcp_marks_web <> '{}'::character varying[]))
  • Rows Removed by Filter: 30
34. 0.000 0.000 ↓ 0.0 0

Index Scan using t_period_pkey on t_period permarks (cost=0.42..1.97 rows=1 width=4) (never executed)

  • Index Cond: (per_id = webmarks.per_id)
  • Filter: per_register