explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NR2p

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.066 ↓ 0.0 0 1

Aggregate (cost=77.58..77.62 rows=1 width=218) (actual time=0.066..0.066 rows=0 loops=1)

  • Buffers: shared hit=9
2. 0.025 0.065 ↓ 0.0 0 1

Sort (cost=77.58..77.59 rows=1 width=218) (actual time=0.065..0.065 rows=0 loops=1)

  • Sort Key: sgac.loc_id, sgac.loc_name, sgac.category
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=9
3. 0.001 0.040 ↓ 0.0 0 1

Subquery Scan on sgac (cost=77.48..77.57 rows=1 width=218) (actual time=0.04..0.04 rows=0 loops=1)

  • Buffers: shared hit=6
4. 0.000 0.039 ↓ 0.0 0 1

Aggregate (cost=77.48..77.56 rows=1 width=414) (actual time=0.039..0.039 rows=0 loops=1)

  • Filter: (((sum(CASE WHEN ((p.per_gender)::text = 'M'::text) THEN 1 ELSE 0 END) >= 1) AND (sum(CASE WHEN ((p.per_gender)::text = 'F'::text) THEN 1 ELSE 0 END) = 0)) OR ((sum(CASE WHEN ((p.per_gender)::text = 'M'::text) THEN 1 ELSE 0 END) = 0) AND (sum(CASE WHEN ((p.per_gender)::text = 'F'::text) THEN 1 ELSE 0 END) >= 1)))
  • Buffers: shared hit=6
5. 0.018 0.039 ↓ 0.0 0 1

Sort (cost=77.48..77.49 rows=1 width=392) (actual time=0.039..0.039 rows=0 loops=1)

  • Sort Key: l.loc_id, "*SELECT* 1".dist_course_fiscal_year, (CASE WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{"Algebra I",Geometry,"Algebra II"}'::text[])) THEN 'Algebra or Geometry'::text WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{"Advanced Math",Calculus,"Other Math"}'::text[])) THEN 'Other Mathematics'::text WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{Biology,Chemistry,Physics,"Other Science"}'::text[])) THEN 'Science'::text WHEN ((si.rfs_ocr_cat_code)::text = 'English/Reading/Language Arts'::text) THEN 'English/Reading/Language Arts'::text WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{"Foreign Lang","Other Academic Subjects"}'::text[])) THEN 'Other Academic Subjects'::text ELSE NULL::text END), "*SELECT* 1".dist_course_id, "*SELECT* 1".loc_crs_section_id, "*SELECT* 1".student_cal_id, lcstp.rfd_cal_day_code_id, lcstp.stu_cal_period_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6
6. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=1.14..77.47 rows=1 width=392) (actual time=0.021..0.021 rows=0 loops=1)

  • Buffers: shared hit=3
7. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.86..69.15 rows=1 width=412) (actual time=0.021..0.021 rows=0 loops=1)

  • Buffers: shared hit=3
8. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.86..68.08 rows=1 width=266) (actual time=0.021..0.021 rows=0 loops=1)

  • Buffers: shared hit=3
9. 0.001 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.71..61.9 rows=1 width=202) (actual time=0.021..0.021 rows=0 loops=1)

  • Buffers: shared hit=3
10. 0.000 0.020 ↓ 0.0 0 1

Nested Loop (cost=0.43..53.57 rows=1 width=262) (actual time=0.02..0.02 rows=0 loops=1)

  • Buffers: shared hit=3
11. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on district_course_catalog dcc (cost=0..1.05 rows=5 width=118) (actual time=0.004..0.005 rows=5 loops=1)

  • Buffers: shared hit=1
12. 0.003 0.015 ↓ 0.0 0 5

Materialize (cost=0.43..52.35 rows=2 width=190) (actual time=0.003..0.003 rows=0 loops=5)

  • Buffers: shared hit=2
13. 0.001 0.012 ↓ 0.0 0 1

Append (cost=0.43..52.34 rows=2 width=190) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=2
14. 0.000 0.005 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.43..27.13 rows=1 width=190) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=1
15. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.43..27.12 rows=1 width=190) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=1
16. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.28..17.15 rows=1 width=190) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=1
17. 0.005 0.005 ↓ 0.0 0 1

Index Scan using xpklocation_cou_04 on location_course_section lcs (cost=0.14..8.3 rows=1 width=136) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (lcs.dist_course_fiscal_year = 2014)
  • Filter: ((lcs.loc_crs_section_gen_restrict)::text = ANY ('{M,F}'::text[]))
  • Buffers: shared hit=1
18. 0.000 0.000 ↓ 0.0 0 0

Index Scan using xpkstudent_class on student_class sc1 (cost=0.14..8.83 rows=1 width=190) (never executed)

  • Index Cond: ((sc1.stu_class_enter_date <= '2013-10-25 00:00:00'::timestamp without time zone) AND (sc1.dist_course_fiscal_year = 2014))
  • Filter: (sc1.stu_class_exit_date >= '2013-10-25 00:00:00'::timestamp without time zone)
19. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using xpklocation_crs_se on location_crs_sec_connection lcsc (cost=0.14..9.96 rows=1 width=136) (never executed)

  • Index Cond: (lcsc.child_dist_course_fiscal_year = 2014)
20. 0.000 0.006 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.43..25.21 rows=1 width=190) (actual time=0.006..0.006 rows=0 loops=1)

  • Buffers: shared hit=1
21. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.43..25.2 rows=1 width=190) (actual time=0.006..0.006 rows=0 loops=1)

  • Buffers: shared hit=1
22. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.29..17.01 rows=1 width=454) (actual time=0.006..0.006 rows=0 loops=1)

  • Buffers: shared hit=1
23. 0.006 0.006 ↓ 0.0 0 1

Index Scan using xpkstudent_class on student_class sc1_1 (cost=0.14..8.83 rows=1 width=190) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((sc1_1.stu_class_enter_date <= '2013-10-25 00:00:00'::timestamp without time zone) AND (sc1_1.dist_course_fiscal_year = 2014))
  • Filter: (sc1_1.stu_class_exit_date >= '2013-10-25 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
24. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using xpklocation_crs_se on location_crs_sec_connection lcsc_1 (cost=0.14..8.17 rows=1 width=268) (never executed)

  • Index Cond: ((lcsc_1.child_loc_id = (sc1_1.loc_id)::text) AND (lcsc_1.child_loc_id IS NOT NULL) AND (lcsc_1.child_cal_id = (sc1_1.student_cal_id)::text) AND (lcsc_1.child_dist_course_id = (sc1_1.dist_course_id)::text) AND (lcsc_1.child_dist_course_fiscal_year = 2014) AND (lcsc_1.child_crs_section_id = (sc1_1.loc_crs_section_id)::text))
25. 0.000 0.000 ↓ 0.0 0 0

Index Scan using xpklocation_cou_04 on location_course_section lcs_1 (cost=0.14..8.17 rows=1 width=136) (never executed)

  • Index Cond: (((lcs_1.loc_crs_section_id)::text = (lcsc_1.child_crs_section_id)::text) AND ((lcs_1.loc_id)::text = (lcsc_1.child_loc_id)::text) AND ((lcs_1.dist_course_id)::text = (lcsc_1.child_dist_course_id)::text) AND (lcs_1.dist_course_fiscal_year = 2014) AND ((lcs_1.student_cal_id)::text = (lcsc_1.child_cal_id)::text))
  • Filter: ((lcs_1.loc_crs_section_gen_restrict)::text = ANY ('{M,F}'::text[]))
26. 0.000 0.000 ↓ 0.0 0 0

Index Scan using xpkrfds_service_id on rfds_service_id si (cost=0.28..8.32 rows=1 width=27) (never executed)

  • Index Cond: (((si.rfds_service_id_code)::text = (dcc.rfds_service_id_code)::text) AND ((si.rfds_service_id_short_desc)::text = (dcc.rfds_service_id_short_desc)::text))
  • Filter: (CASE WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{"Algebra I",Geometry,"Algebra II"}'::text[])) THEN 'Algebra or Geometry'::text WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{"Advanced Math",Calculus,"Other Math"}'::text[])) THEN 'Other Mathematics'::text WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{Biology,Chemistry,Physics,"Other Science"}'::text[])) THEN 'Science'::text WHEN ((si.rfs_ocr_cat_code)::text = 'English/Reading/Language Arts'::text) THEN 'English/Reading/Language Arts'::text WHEN ((si.rfs_ocr_cat_code)::text = ANY ('{"Foreign Lang","Other Academic Subjects"}'::text[])) THEN 'Other Academic Subjects'::text ELSE NULL::text END IS NOT NULL)
27. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using xpklocation_crs_01 on location_crs_sec_term_period lcstp (cost=0.15..6.17 rows=1 width=200) (never executed)

  • Index Cond: ((lcstp.dist_course_id = ("*SELECT* 1".dist_course_id)::text) AND (lcstp.dist_course_fiscal_year = "*SELECT* 1".dist_course_fiscal_year) AND (lcstp.student_cal_term_type = ("*SELECT* 1".student_cal_term_type)::text) AND (lcstp.loc_id = ("*SELECT* 1".loc_id)::text) AND (lcstp.loc_crs_section_id = ("*SELECT* 1".loc_crs_section_id)::text))
28. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on location l (cost=0..1.03 rows=3 width=170) (never executed)

29. 0.000 0.000 ↓ 0.0 0 0

Index Scan using xpkperson on person p (cost=0.28..8.3 rows=1 width=8) (never executed)

  • Index Cond: ((p.per_id)::text = ("*SELECT* 1".per_id)::text)