explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LbM5

Settings
# exclusive inclusive rows x rows loops node
1. 0.129 55,739.666 ↓ 226.0 226 1

Unique (cost=4,326.30..4,326.32 rows=1 width=54) (actual time=55,739.493..55,739.666 rows=226 loops=1)

2. 0.163 55,739.537 ↓ 226.0 226 1

Sort (cost=4,326.30..4,326.30 rows=1 width=54) (actual time=55,739.492..55,739.537 rows=226 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: 56kB
3. 0.590 55,739.374 ↓ 226.0 226 1

GroupAggregate (cost=4,326.23..4,326.29 rows=1 width=54) (actual time=55,738.645..55,739.374 rows=226 loops=1)

  • Group Key: stu.student_id, se.syear
4. 0.756 55,738.784 ↓ 788.0 788 1

Sort (cost=4,326.23..4,326.24 rows=1 width=19) (actual time=55,738.628..55,738.784 rows=788 loops=1)

  • Sort Key: stu.student_id, se.syear
  • Sort Method: quicksort Memory: 86kB
5. 1.023 55,738.028 ↓ 788.0 788 1

Nested Loop Left Join (cost=1,004.01..4,326.22 rows=1 width=19) (actual time=26,206.098..55,738.028 rows=788 loops=1)

6. 0.729 55,733.853 ↓ 788.0 788 1

Nested Loop (cost=1,003.73..4,325.46 rows=1 width=35) (actual time=26,206.070..55,733.853 rows=788 loops=1)

  • Join Filter: (rmp.syear = (ad.syear)::numeric)
7. 1.270 55,730.968 ↓ 154.0 308 1

Nested Loop (cost=1,003.30..4,304.91 rows=2 width=59) (actual time=26,206.054..55,730.968 rows=308 loops=1)

  • Join Filter: ((rmp.syear = mp.syear) AND (SubPlan 1))
  • Rows Removed by Join Filter: 924
8. 3,903.984 55,724.462 ↓ 308.0 308 1

Nested Loop (cost=1,003.15..4,303.09 rows=1 width=64) (actual time=26,206.004..55,724.462 rows=308 loops=1)

  • Join Filter: (stu.student_id = (sbr.source_id)::numeric)
  • Rows Removed by Join Filter: 11317768
9. 34.191 1,918.052 ↓ 36,747.0 36,747 1

Merge Join (cost=1,003.15..3,867.95 rows=1 width=72) (actual time=17.056..1,918.052 rows=36,747 loops=1)

  • Merge Cond: ((rmp.mp)::text = (ssrs.marking_period)::text)
  • Join Filter: (stu.syear = (ssrs.syear)::numeric)
10. 1,069.003 1,883.837 ↓ 9,187.0 36,748 1

Nested Loop (cost=1,000.44..6,729.98 rows=4 width=72) (actual time=17.025..1,883.837 rows=36,748 loops=1)

  • Join Filter: (stu.school_id = rmp.school_id)
  • Rows Removed by Join Filter: 3344075
11. 0.247 0.247 ↑ 3.9 93 1

Index Scan using rptcrd_mp_mps on rptcrd_mp rmp (cost=0.15..49.16 rows=364 width=24) (actual time=0.016..0.247 rows=93 loops=1)

12. 689.566 814.587 ↓ 36,353.0 36,353 93

Materialize (cost=1,000.29..6,675.36 rows=1 width=48) (actual time=0.006..8.759 rows=36,353 loops=93)

13. 0.000 125.021 ↓ 36,747.0 36,747 1

Gather (cost=1,000.29..6,675.36 rows=1 width=48) (actual time=0.509..125.021 rows=36,747 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
14. 204.075 235.812 ↓ 18,374.0 18,374 2

Nested Loop (cost=0.29..5,675.26 rows=1 width=48) (actual time=1.039..235.812 rows=18,374 loops=2)

15. 31.735 31.735 ↓ 167.9 67,818 2

Parallel Seq Scan on student_enrollment se (cost=0.00..3,536.64 rows=404 width=32) (actual time=0.006..31.735 rows=67,818 loops=2)

  • Filter: ((COALESCE(custom_9, 'N'::character varying))::text = 'N'::text)
  • Rows Removed by Filter: 906
16. 0.002 0.002 ↓ 0.0 0 135,635

Index Scan using rptcrd_stu_student_id on rptcrd_es_stu stu (cost=0.29..5.28 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=135,635)

  • Index Cond: (student_id = (se.student_id)::numeric)
  • Filter: (((se.syear)::numeric = syear) AND ((se.school_id)::numeric = school_id))
  • Rows Removed by Filter: 0
17. 0.012 0.024 ↑ 1.0 1 1

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

  • Sort Key: ssrs.marking_period
  • Sort Method: quicksort Memory: 25kB
18. 0.012 0.012 ↑ 1.0 1 1

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

  • Filter: (id = 477)
  • Rows Removed by Filter: 55
19. 49,902.426 49,902.426 ↑ 1.1 308 36,747

Seq Scan on ssrs_batch_record sbr (cost=0.00..430.24 rows=327 width=16) (actual time=1.202..1.358 rows=308 loops=36,747)

  • Filter: (ssrs_batch_id = 477)
  • Rows Removed by Filter: 20303
20. 1.540 1.540 ↑ 1.0 4 308

Index Scan using rptcrd_mp_school_id on rptcrd_mp mp (cost=0.15..0.22 rows=4 width=21) (actual time=0.003..0.005 rows=4 loops=308)

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

SubPlan (forNested Loop)

22. 3.696 3.696 ↑ 100.0 1 1,232

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.002..0.003 rows=1 loops=1,232)

23. 0.000 0.000 ↑ 1.0 1 1,232

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

24. 2.156 2.156 ↓ 3.0 3 308

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.004..0.007 rows=3 loops=308)

  • 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))
25. 3.152 3.152 ↑ 1.0 1 788

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=788)

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