explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LShK

Settings
# exclusive inclusive rows x rows loops node
1. 91.644 1,984.862 ↑ 1.0 174,541 1

Hash Join (cost=12,792.93..48,989.83 rows=174,541 width=578) (actual time=431.819..1,984.862 rows=174,541 loops=1)

  • Buffers: shared hit=96921 read=803
2. 88.674 1,893.152 ↑ 1.0 174,541 1

Hash Join (cost=12,789.25..46,586.2 rows=174,541 width=560) (actual time=431.731..1,893.152 rows=174,541 loops=1)

  • Buffers: shared hit=96919 read=803
3. 82.756 1,804.460 ↑ 1.0 174,541 1

Merge Join (cost=12,787.91..44,184.93 rows=174,541 width=548) (actual time=431.686..1,804.46 rows=174,541 loops=1)

  • Buffers: shared hit=96918 read=803
4. 81.272 102.708 ↓ 1.3 39,129 1

Sort (cost=4,486.39..4,564.26 rows=31,148 width=11) (actual time=96.133..102.708 rows=39,129 loops=1)

  • Sort Key: dccgpa.dist_course_id, dccgpa.dist_course_fiscal_year
  • Sort Method: quicksort Memory: 3200kB
  • Buffers: shared hit=613
5. 9.688 21.436 ↓ 1.3 39,129 1

Nested Loop (cost=809.81..2,161.68 rows=31,148 width=11) (actual time=4.878..21.436 rows=39,129 loops=1)

  • Buffers: shared hit=613
6. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on gpa_calculation_definition gpcd (cost=0..1.04 rows=1 width=15) (actual time=0.008..0.011 rows=1 loops=1)

  • Filter: (gpcd.gpa_calc_def_ranking_flg = 'Y'::bpchar)
  • Buffers: shared hit=1
7. 6.944 11.737 ↓ 1.3 39,129 1

Bitmap Heap Scan on district_crs_catalog_gpa_calc dccgpa (cost=809.81..1,849.16 rows=31,148 width=26) (actual time=4.867..11.737 rows=39,129 loops=1)

  • Buffers: shared hit=612
8. 4.793 4.793 ↓ 1.3 39,129 1

Bitmap Index Scan on ifk_r_183 (cost=0..802.03 rows=31,148 width=0) (actual time=4.793..4.793 rows=39,129 loops=1)

  • Index Cond: ((dccgpa.gpa_calc_def_nm)::text = (gpcd.gpa_calc_def_nm)::text)
  • Buffers: shared hit=196
9. 92.778 1,618.996 ↑ 1.0 174,541 1

Materialize (cost=8,301.52..37,546.81 rows=174,541 width=546) (actual time=335.543..1,618.996 rows=174,541 loops=1)

  • Buffers: shared hit=96305 read=803
10. 89.860 1,526.218 ↑ 1.0 174,541 1

Merge Join (cost=8,301.52..37,110.46 rows=174,541 width=546) (actual time=335.532..1,526.218 rows=174,541 loops=1)

  • Buffers: shared hit=96305 read=803
11. 102.175 1,310.524 ↑ 1.0 174,541 1

Merge Join (cost=5,259.02..33,171.48 rows=174,541 width=258) (actual time=221.544..1,310.524 rows=174,541 loops=1)

  • Buffers: shared hit=96200 read=803
12. 73.722 1,021.986 ↑ 1.0 174,541 1

Merge Join (cost=1,777.87..28,733.47 rows=174,541 width=186) (actual time=62.827..1,021.986 rows=174,541 loops=1)

  • Buffers: shared hit=95455 read=803
13. 101.492 873.691 ↑ 1.0 174,541 1

Merge Join (cost=0.71..26,039.61 rows=174,541 width=58) (actual time=0.029..873.691 rows=174,541 loops=1)

  • Buffers: shared hit=95225 read=803
14. 197.429 197.429 ↑ 1.0 39,150 1

Index Scan using idbi_130909_001_district_course_catalog_010 on district_course_catalog dcc (cost=0.29..5,344.2 rows=39,150 width=22) (actual time=0.012..197.429 rows=39,150 loops=1)

  • Buffers: shared hit=14286 read=148
15. 574.770 574.770 ↑ 1.0 174,541 1

Index Scan using ifk_f_course_is_taught on location_course_catalog lcc (cost=0.42..18,427.43 rows=174,541 width=45) (actual time=0.007..574.77 rows=174,541 loops=1)

  • Buffers: shared hit=80939 read=655
16. 41.084 74.573 ↓ 23.5 84,902 1

Sort (cost=1,777.16..1,786.18 rows=3,606 width=137) (actual time=62.795..74.573 rows=84,902 loops=1)

  • Sort Key: grdlvl.dist_course_id, grdlvl.dist_course_fiscal_year
  • Sort Method: quicksort Memory: 1201kB
  • Buffers: shared hit=230
17. 1.696 33.489 ↓ 3.3 11,795 1

Subquery Scan on grdlvl (cost=1,492..1,564.12 rows=3,606 width=137) (actual time=27.576..33.489 rows=11,795 loops=1)

  • Buffers: shared hit=230
18. 28.467 31.793 ↓ 3.3 11,795 1

Aggregate (cost=1,492..1,528.06 rows=3,606 width=137) (actual time=27.576..31.793 rows=11,795 loops=1)

  • Buffers: shared hit=230
19. 3.326 3.326 ↑ 1.0 36,057 1

Seq Scan on district_crs_catalog_grd_level district_crs_catalog_grd_level (cost=0..590.57 rows=36,057 width=20) (actual time=0.006..3.326 rows=36,057 loops=1)

  • Buffers: shared hit=230
20. 118.589 186.363 ↓ 27.7 191,075 1

Sort (cost=3,481.15..3,498.39 rows=6,898 width=81) (actual time=158.707..186.363 rows=191,075 loops=1)

  • Sort Key: parts.dist_course_id, parts.dist_course_fiscal_year
  • Sort Method: quicksort Memory: 4003kB
  • Buffers: shared hit=745
21. 3.977 67.774 ↓ 5.7 39,132 1

Subquery Scan on parts (cost=2,903.37..3,041.33 rows=6,898 width=81) (actual time=51.934..67.774 rows=39,132 loops=1)

  • Buffers: shared hit=745
22. 57.493 63.797 ↓ 5.7 39,132 1

Aggregate (cost=2,903.37..2,972.35 rows=6,898 width=81) (actual time=51.932..63.797 rows=39,132 loops=1)

  • Buffers: shared hit=745
23. 6.304 6.304 ↑ 1.0 68,982 1

Seq Scan on district_crs_catalog_part prt (cost=0..1,178.82 rows=68,982 width=21) (actual time=0.136..6.304 rows=68,982 loops=1)

  • Buffers: shared hit=745
24. 38.898 125.834 ↓ 67.3 109,987 1

Sort (cost=3,042.5..3,046.59 rows=1,634 width=303) (actual time=113.982..125.834 rows=109,987 loops=1)

  • Sort Key: aar.dist_course_id, aar.dist_course_fiscal_year
  • Sort Method: quicksort Memory: 1626kB
  • Buffers: shared hit=105
25. 1.904 86.936 ↓ 9.7 15,885 1

Subquery Scan on aar (cost=2,922.61..2,955.29 rows=1,634 width=303) (actual time=79.287..86.936 rows=15,885 loops=1)

  • Buffers: shared hit=105
26. 28.210 85.032 ↓ 9.7 15,885 1

Aggregate (cost=2,922.61..2,938.95 rows=1,634 width=303) (actual time=79.285..85.032 rows=15,885 loops=1)

  • Buffers: shared hit=105
27. 9.711 56.822 ↑ 1.0 16,336 1

WindowAgg (cost=1,411.53..1,860.77 rows=16,336 width=25) (actual time=45.53..56.822 rows=16,336 loops=1)

  • Buffers: shared hit=105
28. 45.754 47.111 ↑ 1.0 16,336 1

Sort (cost=1,411.53..1,452.37 rows=16,336 width=17) (actual time=45.518..47.111 rows=16,336 loops=1)

  • Sort Key: location_crs_aar_program_code.dist_course_fiscal_year, location_crs_aar_program_code.loc_id, location_crs_aar_program_code.student_cal_id, location_crs_aar_program_code.dist_course_id, location_crs_aar_program_code.rfd_aar_prog_code_id
  • Sort Method: quicksort Memory: 1661kB
  • Buffers: shared hit=105
29. 1.357 1.357 ↑ 1.0 16,336 1

Seq Scan on location_crs_aar_program_code location_crs_aar_program_code (cost=0..268.36 rows=16,336 width=17) (actual time=0.011..1.357 rows=16,336 loops=1)

  • Buffers: shared hit=105
30. 0.006 0.018 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=14) (actual time=0.018..0.018 rows=15 loops=1)

  • Buffers: shared hit=1
31. 0.012 0.012 ↑ 1.0 15 1

Seq Scan on rfd_transcript_printing_cat tpc (cost=0..1.15 rows=15 width=14) (actual time=0.01..0.012 rows=15 loops=1)

  • Buffers: shared hit=1
32. 0.036 0.066 ↓ 1.0 77 1

Hash (cost=2.75..2.75 rows=75 width=22) (actual time=0.066..0.066 rows=77 loops=1)

  • Buffers: shared hit=2
33. 0.030 0.030 ↓ 1.0 77 1

Seq Scan on location l (cost=0..2.75 rows=75 width=22) (actual time=0.011..0.03 rows=77 loops=1)

  • Buffers: shared hit=2