explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6sh

Settings
# exclusive inclusive rows x rows loops node
1. 0.777 3,047.878 ↑ 5.6 1,307 1

Sort (cost=992,660.94..992,679.25 rows=7,324 width=128) (actual time=3,047.699..3,047.878 rows=1,307 loops=1)

  • Sort Key: _pitch.pitch_n
  • Sort Method: quicksort Memory: 393kB
2.          

CTE _pitch

3. 2.053 3,045.925 ↑ 5.6 1,308 1

WindowAgg (cost=590,262.51..992,043.58 rows=7,361 width=128) (actual time=1,120.934..3,045.925 rows=1,308 loops=1)

4. 210.817 3,043.872 ↑ 5.6 1,308 1

GroupAggregate (cost=590,262.51..991,693.93 rows=7,361 width=116) (actual time=1,120.912..3,043.872 rows=1,308 loops=1)

  • Group Key: pitch.pitcher_id
5. 147.495 1,339.999 ↑ 1.0 373,264 1

Merge Left Join (cost=590,262.51..593,615.58 rows=377,709 width=57) (actual time=1,115.100..1,339.999 rows=373,264 loops=1)

  • Merge Cond: ((pitch.pitcher_id = vwm_value_pitcher_scale.pitcher_id) AND (pitch.batter_side = vwm_value_pitcher_scale.batter_side))
  • Join Filter: (pitch.year = vwm_value_pitcher_scale.year)
6. 390.479 1,185.907 ↑ 1.0 373,264 1

Sort (cost=589,051.10..589,995.37 rows=377,709 width=53) (actual time=1,108.769..1,185.907 rows=373,264 loops=1)

  • Sort Key: pitch.pitcher_id, pitch.batter_side
  • Sort Method: quicksort Memory: 64,464kB
7. 678.090 795.428 ↑ 1.0 373,264 1

Bitmap Heap Scan on pitch (cost=9,219.81..554,062.19 rows=377,709 width=53) (actual time=164.766..795.428 rows=373,264 loops=1)

  • Recheck Cond: (year = 2,020)
  • Heap Blocks: exact=195,616
8. 117.338 117.338 ↓ 3.7 1,402,139 1

Bitmap Index Scan on _ix_bluemedia_42pro_pitch_year (cost=0.00..9,125.38 rows=377,709 width=0) (actual time=117.338..117.338 rows=1,402,139 loops=1)

  • Index Cond: (year = 2,020)
9. 0.771 6.597 ↑ 1.0 1,463 1

Sort (cost=1,211.41..1,215.16 rows=1,497 width=16) (actual time=6.318..6.597 rows=1,463 loops=1)

  • Sort Key: vwm_value_pitcher_scale.pitcher_id, vwm_value_pitcher_scale.batter_side
  • Sort Method: quicksort Memory: 117kB
10. 5.826 5.826 ↑ 1.0 1,463 1

Seq Scan on vwm_value_pitcher_scale (cost=0.00..1,132.46 rows=1,497 width=16) (actual time=0.025..5.826 rows=1,463 loops=1)

  • Filter: (year = 2,020)
  • Rows Removed by Filter: 56,174
11.          

SubPlan (for GroupAggregate)

12. 1,493.056 1,493.056 ↑ 11.0 1 373,264

Index Only Scan using _ix_bluemedia_42pro_video_pitch_id on video (cost=0.56..4.76 rows=11 width=0) (actual time=0.004..0.004 rows=1 loops=373,264)

  • Index Cond: (pitch_id = pitch.pitch_id)
  • Heap Fetches: 2,705
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_bluemedia_42pro_video_pitch_id on video video_1 (cost=0.56..1,565,799.31 rows=37,870,584 width=16) (never executed)

  • Heap Fetches: 0
14. 3,047.101 3,047.101 ↑ 5.6 1,307 1

CTE Scan on _pitch (cost=0.00..147.22 rows=7,324 width=128) (actual time=1,120.937..3,047.101 rows=1,307 loops=1)

  • Filter: (pitcher_id IS NOT NULL)
  • Rows Removed by Filter: 1
Planning time : 0.662 ms
Execution time : 3,052.086 ms