explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kxRR

Settings
# exclusive inclusive rows x rows loops node
1. 268,019.046 268,019.046 ↑ 1.2 6,247 1

CTE Scan on _pitch (cost=7,884,237.12..7,884,384.34 rows=7,361 width=120) (actual time=230,528.867..268,019.046 rows=6,247 loops=1)

2.          

CTE _pitch

3. 4,212.808 268,009.233 ↑ 1.2 6,247 1

GroupAggregate (cost=2,614,199.72..7,884,237.12 rows=7,361 width=120) (actual time=230,528.864..268,009.233 rows=6,247 loops=1)

  • Group Key: pitch.pitcher_id
4. 2,010.158 233,911.103 ↓ 1.0 4,980,887 1

Merge Left Join (cost=2,614,199.72..2,709,359.74 rows=4,911,096 width=57) (actual time=230,528.689..233,911.103 rows=4,980,887 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)
5. 6,179.188 231,882.962 ↓ 1.0 4,980,887 1

Sort (cost=2,612,185.98..2,624,463.72 rows=4,911,096 width=53) (actual time=230,514.389..231,882.962 rows=4,980,887 loops=1)

  • Sort Key: pitch.pitcher_id, pitch.batter_side
  • Sort Method: quicksort Memory: 858,859kB
6. 210,559.894 225,703.774 ↓ 1.0 4,980,887 1

Bitmap Heap Scan on pitch (cost=119,855.56..2,066,376.26 rows=4,911,096 width=53) (actual time=15,597.880..225,703.774 rows=4,980,887 loops=1)

  • Recheck Cond: (year = 2,019)
  • Heap Blocks: exact=1,111,947
7. 15,143.880 15,143.880 ↓ 1.0 4,980,887 1

Bitmap Index Scan on _ix_bluemedia_42pro_pitch_year (cost=0.00..118,627.78 rows=4,911,096 width=0) (actual time=15,143.880..15,143.880 rows=4,980,887 loops=1)

  • Index Cond: (year = 2,019)
8. 8.226 17.983 ↓ 1.0 12,936 1

Sort (cost=2,013.74..2,046.01 rows=12,907 width=16) (actual time=14.284..17.983 rows=12,936 loops=1)

  • Sort Key: vwm_value_pitcher_scale.pitcher_id, vwm_value_pitcher_scale.batter_side
  • Sort Method: quicksort Memory: 991kB
9. 9.757 9.757 ↓ 1.0 12,939 1

Seq Scan on vwm_value_pitcher_scale (cost=0.00..1,132.46 rows=12,907 width=16) (actual time=0.947..9.757 rows=12,939 loops=1)

  • Filter: (year = 2,019)
  • Rows Removed by Filter: 44,698
10.          

SubPlan (for GroupAggregate)

11. 29,885.322 29,885.322 ↑ 11.0 1 4,980,887

Index Only Scan using _ix_bluemedia_42pro_video_pitch_id on video (cost=0.56..4.76 rows=11 width=0) (actual time=0.006..0.006 rows=1 loops=4,980,887)

  • Index Cond: (pitch_id = pitch.pitch_id)
  • Heap Fetches: 3,077
12. 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
Planning time : 0.575 ms
Execution time : 268,073.988 ms