explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QPd8

Settings
# exclusive inclusive rows x rows loops node
1. 0.250 246.390 ↓ 472.0 472 1

Unique (cost=2,719.37..2,719.40 rows=1 width=54) (actual time=246.048..246.390 rows=472 loops=1)

2. 0.311 246.140 ↓ 472.0 472 1

Sort (cost=2,719.37..2,719.38 rows=1 width=54) (actual time=246.047..246.140 rows=472 loops=1)

  • Sort Key: stu.student_id, se.syear, (sum(CASE WHEN (((mp.mp)::text = 'Q1'::text) AND (ac.state_code = 'A'::bpchar)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((mp.mp)::text = 'Q2'::text) AND (ac.state_code = 'A'::bpchar)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((mp.mp)::text = 'Q3'::text) AND (ac.state_code = 'A'::bpchar)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((mp.mp)::text = 'Q4'::text) AND (ac.state_code = 'A'::bpchar)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (ac.state_code = 'A'::bpchar) THEN 1 ELSE 0 END))
  • Sort Method: quicksort Memory: 91kB
3. 1.469 245.829 ↓ 472.0 472 1

GroupAggregate (cost=2,719.31..2,719.36 rows=1 width=54) (actual time=243.993..245.829 rows=472 loops=1)

  • Group Key: stu.student_id, se.syear
4. 1.573 244.360 ↓ 2,009.0 2,009 1

Sort (cost=2,719.31..2,719.31 rows=1 width=19) (actual time=243.965..244.360 rows=2,009 loops=1)

  • Sort Key: stu.student_id, se.syear
  • Sort Method: quicksort Memory: 205kB
5. 1.656 242.787 ↓ 2,009.0 2,009 1

Nested Loop Left Join (cost=434.37..2,719.30 rows=1 width=19) (actual time=160.542..242.787 rows=2,009 loops=1)

6. 1.900 233.095 ↓ 2,009.0 2,009 1

Nested Loop (cost=434.09..2,718.54 rows=1 width=35) (actual time=160.527..233.095 rows=2,009 loops=1)

  • Join Filter: (rmp.syear = (ad.syear)::numeric)
7. 4.176 226.667 ↓ 566.0 1,132 1

Nested Loop (cost=433.66..2,697.99 rows=2 width=59) (actual time=160.505..226.667 rows=1,132 loops=1)

  • Join Filter: ((rmp.syear = mp.syear) AND (SubPlan 1))
  • Rows Removed by Join Filter: 1132
8. 1.143 215.699 ↓ 566.0 566 1

Nested Loop (cost=433.51..2,696.17 rows=1 width=64) (actual time=160.463..215.699 rows=566 loops=1)

  • Join Filter: ((rmp.school_id = stu.school_id) AND ((sbr.source_id)::numeric = stu.student_id) AND ((ssrs.syear)::numeric = stu.syear))
9. 1.064 212.858 ↓ 566.0 566 1

Merge Join (cost=433.22..2,690.86 rows=1 width=69) (actual time=160.434..212.858 rows=566 loops=1)

  • Merge Cond: ((rmp.mp)::text = (ssrs.marking_period)::text)
  • Join Filter: ((se.syear)::numeric = (ssrs.syear)::numeric)
  • Rows Removed by Join Filter: 516
10. 70.742 211.648 ↓ 34.9 2,165 1

Nested Loop (cost=430.51..4,944.91 rows=62 width=72) (actual time=103.338..211.648 rows=2,165 loops=1)

  • Join Filter: ((se.school_id)::numeric = rmp.school_id)
  • Rows Removed by Join Filter: 202004
11. 0.101 0.101 ↑ 1.9 189 1

Index Scan using rptcrd_mp_mps on rptcrd_mp rmp (cost=0.15..49.16 rows=364 width=24) (actual time=0.014..0.101 rows=189 loops=1)

12. 40.181 140.805 ↓ 31.8 1,080 189

Materialize (cost=430.36..4,679.25 rows=34 width=48) (actual time=0.064..0.745 rows=1,080 loops=189)

13. 43.902 100.624 ↓ 31.8 1,082 1

Hash Join (cost=430.36..4,679.08 rows=34 width=48) (actual time=11.980..100.624 rows=1,082 loops=1)

  • Hash Cond: ((se.student_id)::numeric = (sbr.source_id)::numeric)
14. 54.882 54.882 ↓ 197.4 135,635 1

Seq Scan on student_enrollment se (cost=0.00..4,244.09 rows=687 width=32) (actual time=0.007..54.882 rows=135,635 loops=1)

  • Filter: ((COALESCE(custom_9, 'N'::character varying))::text = 'N'::text)
  • Rows Removed by Filter: 1812
15. 0.226 1.840 ↓ 56.6 566 1

Hash (cost=430.24..430.24 rows=10 width=16) (actual time=1.840..1.840 rows=566 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
16. 1.614 1.614 ↓ 56.6 566 1

Seq Scan on ssrs_batch_record sbr (cost=0.00..430.24 rows=10 width=16) (actual time=1.403..1.614 rows=566 loops=1)

  • Filter: (ssrs_batch_id = 484)
  • Rows Removed by Filter: 20045
17. 0.115 0.146 ↑ 1.0 1 1

Materialize (cost=2.71..2.72 rows=1 width=19) (actual time=0.036..0.146 rows=1 loops=1)

18. 0.017 0.031 ↑ 1.0 1 1

Sort (cost=2.71..2.71 rows=1 width=19) (actual time=0.031..0.031 rows=1 loops=1)

  • Sort Key: ssrs.marking_period
  • Sort Method: quicksort Memory: 25kB
19. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on ssrs_batch ssrs (cost=0.00..2.70 rows=1 width=19) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: (id = 484)
  • Rows Removed by Filter: 55
20. 1.698 1.698 ↑ 1.0 1 566

Index Scan using rptcrd_stu_student_id on rptcrd_es_stu stu (cost=0.29..5.28 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=566)

  • Index Cond: (student_id = (se.student_id)::numeric)
  • Filter: (((se.syear)::numeric = syear) AND ((se.school_id)::numeric = school_id))
21. 2.264 2.264 ↑ 1.0 4 566

Index Scan using rptcrd_mp_school_id on rptcrd_mp mp (cost=0.15..0.22 rows=4 width=21) (actual time=0.002..0.004 rows=4 loops=566)

  • Index Cond: (school_id = stu.school_id)
22.          

SubPlan (forNested Loop)

23. 4.528 4.528 ↑ 50.0 2 2,264

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.002..0.002 rows=2 loops=2,264)

24. 0.000 0.000 ↑ 1.0 1 2,264

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=2,264)

25. 4.528 4.528 ↓ 2.0 2 1,132

Index Scan using "f:u:attendance_day:student_id,school_date" on attendance_day ad (cost=0.43..10.26 rows=1 width=22) (actual time=0.003..0.004 rows=2 loops=1,132)

  • Index Cond: ((student_id = se.student_id) AND (school_date >= se.start_date) AND (school_date >= mp.start_date) AND (school_date <= mp.end_date) AND (school_date >= se.start_date) AND (school_date <= COALESCE(se.end_date, mp.end_date)))
  • Filter: ((se.syear = syear) AND (mp.syear = (syear)::numeric))
26. 8.036 8.036 ↑ 1.0 1 2,009

Index Scan using "f:i:attendance_codes:syear,school_id" on attendance_codes ac (cost=0.28..0.75 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=2,009)

  • Index Cond: ((ad.syear = syear) AND (se.school_id = school_id))
  • Filter: ((ad.daily_code)::text = (short_name)::text)
  • Rows Removed by Filter: 10