explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rUtY

Settings
# exclusive inclusive rows x rows loops node
1. 46.807 46.807 ↓ 18.0 18 1

CTE Scan on final (cost=4,128.64..4,129.39 rows=1 width=532) (actual time=46.757..46.807 rows=18 loops=1)

  • Filter: ((pa_of_query >= 15) AND (pa_of_query <= 19))
  • Rows Removed by Filter: 42
2.          

CTE _pitch

3. 0.091 44.241 ↓ 4.0 60 1

WindowAgg (cost=3,277.37..3,278.15 rows=15 width=321) (actual time=44.158..44.241 rows=60 loops=1)

4. 0.086 44.150 ↓ 4.0 60 1

Sort (cost=3,277.37..3,277.40 rows=15 width=315) (actual time=44.145..44.150 rows=60 loops=1)

  • Sort Key: pitch.game_date DESC, pitch.game_id DESC, pitch.inning, pitch.inning_top DESC, pitch.pa_of_inning, pitch.pitch_of_pa
  • Sort Method: quicksort Memory: 41kB
5. 0.078 44.064 ↓ 4.0 60 1

WindowAgg (cost=3,276.77..3,277.07 rows=15 width=315) (actual time=43.995..44.064 rows=60 loops=1)

6. 0.069 43.986 ↓ 4.0 60 1

Sort (cost=3,276.77..3,276.81 rows=15 width=307) (actual time=43.981..43.986 rows=60 loops=1)

  • Sort Key: pitch.pa_id, pitch.pitch_of_pa
  • Sort Method: quicksort Memory: 40kB
7. 0.354 43.917 ↓ 4.0 60 1

Bitmap Heap Scan on pitch (cost=3,244.25..3,276.48 rows=15 width=307) (actual time=43.797..43.917 rows=60 loops=1)

  • Recheck Cond: ((pitcher_id = 38,155) AND (year = 2,020))
  • Filter: (game_type = 'R'::text)
  • Rows Removed by Filter: 158
  • Heap Blocks: exact=198
8. 0.666 43.563 ↓ 0.0 0 1

BitmapAnd (cost=3,244.25..3,244.25 rows=16 width=0) (actual time=43.563..43.563 rows=0 loops=1)

9. 0.891 0.891 ↓ 2.5 7,251 1

Bitmap Index Scan on ix_bluemedia_42pro_pitch_pitcher_id (cost=0.00..76.31 rows=2,899 width=0) (actual time=0.891..0.891 rows=7,251 loops=1)

  • Index Cond: (pitcher_id = 38,155)
10. 42.006 42.006 ↓ 3.7 481,774 1

Bitmap Index Scan on _ix_bluemedia_42pro_pitch_year (cost=0.00..3,167.68 rows=131,883 width=0) (actual time=42.006..42.006 rows=481,774 loops=1)

  • Index Cond: (year = 2,020)
11.          

CTE _video

12. 1.241 1.892 ↑ 6.6 60 1

HashAggregate (cost=833.40..838.33 rows=394 width=48) (actual time=1.868..1.892 rows=60 loops=1)

  • Group Key: video.pitch_id
13. 0.133 0.651 ↑ 1.2 331 1

Nested Loop (cost=0.56..829.46 rows=394 width=116) (actual time=0.024..0.651 rows=331 loops=1)

14. 0.038 0.038 ↓ 4.0 60 1

CTE Scan on _pitch (cost=0.00..0.30 rows=15 width=16) (actual time=0.004..0.038 rows=60 loops=1)

15. 0.480 0.480 ↑ 4.3 6 60

Index Scan using _ix_bluemedia_42pro_video_pitch_id on video (cost=0.56..55.02 rows=26 width=116) (actual time=0.005..0.008 rows=6 loops=60)

  • Index Cond: (pitch_id = _pitch.pitch_id)
16.          

CTE final

17. 0.093 46.714 ↓ 2.0 60 1

WindowAgg (cost=11.26..12.16 rows=30 width=532) (actual time=46.630..46.714 rows=60 loops=1)

18. 0.106 46.621 ↓ 2.0 60 1

Sort (cost=11.26..11.33 rows=30 width=524) (actual time=46.617..46.621 rows=60 loops=1)

  • Sort Key: _pitch_1.game_date DESC, _pitch_1.game_id DESC, _pitch_1.inning, _pitch_1.inning_top DESC, _pitch_1.pa_of_inning, _pitch_1.pitch_of_pa
  • Sort Method: quicksort Memory: 144kB
19. 0.135 46.515 ↓ 2.0 60 1

WindowAgg (cost=0.49..10.52 rows=30 width=524) (actual time=46.481..46.515 rows=60 loops=1)

20. 0.069 46.380 ↓ 2.0 60 1

Hash Right Join (cost=0.49..10.15 rows=30 width=516) (actual time=46.272..46.380 rows=60 loops=1)

  • Hash Cond: (_video.pitch_id = _pitch_1.pitch_id)
21. 1.931 1.931 ↑ 6.6 60 1

CTE Scan on _video (cost=0.00..7.88 rows=394 width=48) (actual time=1.870..1.931 rows=60 loops=1)

22. 0.043 44.380 ↓ 4.0 60 1

Hash (cost=0.30..0.30 rows=15 width=484) (actual time=44.380..44.380 rows=60 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
23. 44.337 44.337 ↓ 4.0 60 1

CTE Scan on _pitch _pitch_1 (cost=0.00..0.30 rows=15 width=484) (actual time=44.165..44.337 rows=60 loops=1)

Planning time : 0.576 ms
Execution time : 46.988 ms