explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rn2g

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 48.455 ↓ 1.6 1,000 1

Limit (cost=27,302.02..27,303.54 rows=607 width=504) (actual time=48.204..48.455 rows=1,000 loops=1)

2. 4.553 48.377 ↓ 1.6 1,000 1

Sort (cost=27,302.02..27,303.54 rows=607 width=504) (actual time=48.203..48.377 rows=1,000 loops=1)

  • Sort Key: aggregated_plays.game_date DESC, aggregated_plays.matchup_per_game, aggregated_pitches.pitch_per_atbat
  • Sort Method: quicksort Memory: 1574kB
3. 3.033 43.824 ↓ 2.6 1,555 1

Nested Loop (cost=1.28..27,273.96 rows=607 width=504) (actual time=0.546..43.824 rows=1,555 loops=1)

4. 4.660 29.906 ↓ 2.6 1,555 1

Nested Loop (cost=0.84..12,572.09 rows=603 width=423) (actual time=0.522..29.906 rows=1,555 loops=1)

  • Join Filter: (aggregated_pitches.pitch_result = mlbam_lk_pitch_code.pitch_code)
  • Rows Removed by Join Filter: 35765
5. 1.160 22.136 ↓ 2.6 1,555 1

Nested Loop Left Join (cost=0.84..12,353.71 rows=603 width=423) (actual time=0.509..22.136 rows=1,555 loops=1)

6. 0.874 14.756 ↓ 2.6 1,555 1

Nested Loop Left Join (cost=0.42..7,346.36 rows=603 width=428) (actual time=0.496..14.756 rows=1,555 loops=1)

7. 0.205 7.662 ↓ 2.6 1,555 1

Append (cost=0.00..2,377.50 rows=603 width=396) (actual time=0.474..7.662 rows=1,555 loops=1)

8. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: ((batter_mlb_id = 543333) AND (season = 2019))
9. 7.123 7.454 ↓ 2.6 1,555 1

Bitmap Heap Scan on aggregated_pitches_2019 (cost=29.10..2,377.50 rows=602 width=396) (actual time=0.471..7.454 rows=1,555 loops=1)

  • Recheck Cond: (batter_mlb_id = 543333)
  • Filter: (season = 2019)
  • Heap Blocks: exact=761
10. 0.331 0.331 ↓ 2.7 1,638 1

Bitmap Index Scan on aggregated_pitches_2019_batter_mlb_id_idx (cost=0.00..28.95 rows=602 width=0) (actual time=0.331..0.331 rows=1,638 loops=1)

  • Index Cond: (batter_mlb_id = 543333)
11. 6.220 6.220 ↓ 0.0 0 1,555

Index Scan using pk_generic_statcast_fielding_outfield_pbp on statcast_fielding_outfield_pbp (cost=0.42..8.23 rows=1 width=73) (actual time=0.004..0.004 rows=0 loops=1,555)

  • Index Cond: ((aggregated_pitches.game_pk = game_pk) AND (aggregated_pitches.sc_guid = guid))
12. 6.220 6.220 ↓ 0.0 0 1,555

Index Scan using pk_generic_statcast_fielding_infield_pbp on statcast_fielding_infield_pbp (cost=0.42..8.29 rows=1 width=73) (actual time=0.004..0.004 rows=0 loops=1,555)

  • Index Cond: ((aggregated_pitches.game_pk = game_pk) AND (aggregated_pitches.sc_guid = guid))
13. 3.101 3.110 ↑ 1.0 24 1,555

Materialize (cost=0.00..1.36 rows=24 width=2) (actual time=0.000..0.002 rows=24 loops=1,555)

14. 0.009 0.009 ↑ 1.0 24 1

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

15. 10.885 10.885 ↑ 5.0 1 1,555

Index Scan using aggregated_plays_game_pk_matchup_per_game_idx on aggregated_plays (cost=0.43..24.33 rows=5 width=87) (actual time=0.004..0.007 rows=1 loops=1,555)

  • Index Cond: ((game_pk = aggregated_pitches.game_pk) AND (matchup_per_game = aggregated_pitches.atbat_per_game))
Planning time : 5.741 ms
Execution time : 48.733 ms