explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n9ZY

Settings
# exclusive inclusive rows x rows loops node
1. 0.239 12,158.514 ↑ 1.0 1,000 1

Limit (cost=191,425.65..191,428.15 rows=1,000 width=412) (actual time=12,157.977..12,158.514 rows=1,000 loops=1)

2. 19.164 12,158.275 ↑ 13.7 1,000 1

Sort (cost=191,425.65..191,459.93 rows=13,712 width=412) (actual time=12,157.975..12,158.275 rows=1,000 loops=1)

  • Sort Key: aggregated_plays.game_date DESC, aggregated_plays.matchup_per_game, aggregated_pitches.pitch_per_atbat
  • Sort Method: top-N heapsort Memory: 1035kB
3. 27.933 12,139.111 ↑ 1.1 12,712 1

Nested Loop (cost=1.98..190,673.84 rows=13,712 width=412) (actual time=19.672..12,139.111 rows=12,712 loops=1)

4. 11.852 10,140.818 ↑ 1.1 12,712 1

Hash Join (cost=1.54..26,806.38 rows=13,575 width=331) (actual time=17.631..10,140.818 rows=12,712 loops=1)

  • Hash Cond: (aggregated_pitches.pitch_result = mlbam_lk_pitch_code.pitch_code)
5. 5.032 10,128.938 ↑ 1.1 12,712 1

Append (cost=0.00..26,618.18 rows=13,575 width=331) (actual time=17.570..10,128.938 rows=12,712 loops=1)

6. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on aggregated_pitches (cost=0.00..0.00 rows=1 width=328) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (batter_mlb_id = 641355)
7. 883.981 895.594 ↑ 1.4 3,057 1

Bitmap Heap Scan on aggregated_pitches_2018 (cost=110.91..8,186.33 rows=4,191 width=347) (actual time=17.567..895.594 rows=3,057 loops=1)

  • Recheck Cond: (batter_mlb_id = 641355)
  • Heap Blocks: exact=1816
8. 11.613 11.613 ↑ 1.1 3,755 1

Bitmap Index Scan on aggregated_pitches_2018_batter_mlb_id_idx (cost=0.00..109.87 rows=4,191 width=0) (actual time=11.613..11.613 rows=3,755 loops=1)

  • Index Cond: (batter_mlb_id = 641355)
9. 3,932.713 3,950.926 ↑ 1.2 3,052 1

Bitmap Heap Scan on aggregated_pitches_2017 (cost=118.06..7,057.62 rows=3,565 width=322) (actual time=19.644..3,950.926 rows=3,052 loops=1)

  • Recheck Cond: (batter_mlb_id = 641355)
  • Heap Blocks: exact=1413
10. 18.213 18.213 ↑ 1.2 3,052 1

Bitmap Index Scan on aggregated_pitches_2017_batter_mlb_id_idx (cost=0.00..117.17 rows=3,565 width=0) (actual time=18.213..18.213 rows=3,052 loops=1)

  • Index Cond: (batter_mlb_id = 641355)
11. 4,651.952 4,651.952 ↓ 2.8 2,637 1

Index Scan using aggregated_pitches_2016_batter_mlb_id_idx on aggregated_pitches_2016 (cost=0.43..1,733.41 rows=943 width=326) (actual time=24.401..4,651.952 rows=2,637 loops=1)

  • Index Cond: (batter_mlb_id = 641355)
12. 11.146 41.271 ↓ 1.7 1,487 1

Bitmap Heap Scan on aggregated_pitches_2015 (cost=29.21..1,755.83 rows=875 width=304) (actual time=30.248..41.271 rows=1,487 loops=1)

  • Recheck Cond: (batter_mlb_id = 641355)
  • Heap Blocks: exact=852
13. 30.125 30.125 ↓ 1.9 1,631 1

Bitmap Index Scan on aggregated_pitches_2015_batter_mlb_id_idx (cost=0.00..28.99 rows=875 width=0) (actual time=30.125..30.125 rows=1,631 loops=1)

  • Index Cond: (batter_mlb_id = 641355)
14. 3.154 5.899 ↑ 2.0 399 1

Bitmap Heap Scan on aggregated_pitches_2014 (cost=26.73..1,631.96 rows=813 width=300) (actual time=2.791..5.899 rows=399 loops=1)

  • Recheck Cond: (batter_mlb_id = 641355)
  • Heap Blocks: exact=242
15. 2.745 2.745 ↑ 2.0 399 1

Bitmap Index Scan on aggregated_pitches_2014_batter_mlb_id_idx (cost=0.00..26.53 rows=813 width=0) (actual time=2.745..2.745 rows=399 loops=1)

  • Index Cond: (batter_mlb_id = 641355)
16. 120.008 123.663 ↑ 2.0 412 1

Bitmap Heap Scan on aggregated_pitches_2013 (cost=22.68..1,614.90 rows=807 width=265) (actual time=4.532..123.663 rows=412 loops=1)

  • Recheck Cond: (batter_mlb_id = 641355)
  • Heap Blocks: exact=215
17. 3.655 3.655 ↑ 2.0 412 1

Bitmap Index Scan on aggregated_pitches_2013_batter_mlb_id_idx (cost=0.00..22.48 rows=807 width=0) (actual time=3.655..3.655 rows=412 loops=1)

  • Index Cond: (batter_mlb_id = 641355)
18. 445.288 454.599 ↑ 1.4 1,668 1

Bitmap Heap Scan on aggregated_pitches_2019 (cost=54.87..4,638.13 rows=2,380 width=361) (actual time=10.279..454.599 rows=1,668 loops=1)

  • Recheck Cond: (batter_mlb_id = 641355)
  • Heap Blocks: exact=877
19. 9.311 9.311 ↑ 1.3 1,895 1

Bitmap Index Scan on aggregated_pitches_2019_batter_mlb_id_idx (cost=0.00..54.28 rows=2,380 width=0) (actual time=9.311..9.311 rows=1,895 loops=1)

  • Index Cond: (batter_mlb_id = 641355)
20. 0.016 0.028 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=2) (actual time=0.028..0.028 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.012 0.012 ↑ 1.0 24 1

Seq Scan on mlbam_lk_pitch_code (cost=0.00..1.24 rows=24 width=2) (actual time=0.007..0.012 rows=24 loops=1)

22. 1,970.360 1,970.360 ↑ 5.0 1 12,712

Index Scan using aggregated_plays_game_pk_matchup_per_game_idx on aggregated_plays (cost=0.43..12.02 rows=5 width=87) (actual time=0.153..0.155 rows=1 loops=12,712)

  • Index Cond: ((game_pk = aggregated_pitches.game_pk) AND (matchup_per_game = aggregated_pitches.atbat_per_game))
Planning time : 117.881 ms
Execution time : 12,159.195 ms