explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jGPE

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,472.742 ↑ 2.0 1 1

Limit (cost=35,964.38..35,964.38 rows=2 width=483) (actual time=1,472.741..1,472.742 rows=1 loops=1)

2. 0.046 1,472.740 ↑ 2.0 1 1

Sort (cost=35,964.38..35,964.38 rows=2 width=483) (actual time=1,472.739..1,472.740 rows=1 loops=1)

  • Sort Key: aggregated_plays.game_date DESC, aggregated_plays.matchup_per_game, aggregated_pitches.pitch_per_atbat
  • Sort Method: quicksort Memory: 26kB
3. 0.008 1,472.694 ↑ 2.0 1 1

Nested Loop Left Join (cost=1.28..35,964.37 rows=2 width=483) (actual time=676.292..1,472.694 rows=1 loops=1)

  • Join Filter: (aggregated_pitches.sc_guid = statcast_fielding_infield_pbp.guid)
4. 0.005 69.941 ↑ 2.0 1 1

Nested Loop Left Join (cost=0.86..15,990.58 rows=2 width=485) (actual time=69.924..69.941 rows=1 loops=1)

  • Join Filter: (aggregated_pitches.sc_guid = statcast_fielding_outfield_pbp.guid)
5. 0.024 14.961 ↑ 2.0 1 1

Nested Loop (cost=0.43..31.54 rows=2 width=453) (actual time=14.945..14.961 rows=1 loops=1)

  • Join Filter: (aggregated_pitches.pitch_result = mlbam_lk_pitch_code.pitch_code)
  • Rows Removed by Join Filter: 23
6. 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.004..0.009 rows=24 loops=1)

7. 0.018 14.928 ↑ 2.0 1 24

Materialize (cost=0.43..29.59 rows=2 width=453) (actual time=0.622..0.622 rows=1 loops=24)

8. 0.005 14.910 ↑ 2.0 1 1

Nested Loop (cost=0.43..29.58 rows=2 width=453) (actual time=14.908..14.910 rows=1 loops=1)

9. 0.001 7.011 ↑ 2.0 1 1

Append (cost=0.00..4.58 rows=2 width=372) (actual time=7.010..7.011 rows=1 loops=1)

10. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((sc_guid = '49671d5a-dec3-4307-9995-725a991d5114'::text) AND (season = 2018))
11. 7.009 7.009 ↑ 1.0 1 1

Index Scan using aggregated_pitches_2018_sc_guid_idx on aggregated_pitches_2018 (cost=0.56..4.58 rows=1 width=384) (actual time=7.008..7.009 rows=1 loops=1)

  • Index Cond: (sc_guid = '49671d5a-dec3-4307-9995-725a991d5114'::text)
  • Filter: (season = 2018)
12. 7.894 7.894 ↑ 5.0 1 1

Index Scan using aggregated_plays_game_pk_matchup_per_game_idx on aggregated_plays (cost=0.43..12.45 rows=5 width=87) (actual time=7.893..7.894 rows=1 loops=1)

  • Index Cond: ((game_pk = aggregated_pitches.game_pk) AND (matchup_per_game = aggregated_pitches.atbat_per_game))
13. 0.006 54.975 ↓ 0.0 0 1

Materialize (cost=0.42..15,959.01 rows=1 width=69) (actual time=54.975..54.975 rows=0 loops=1)

14. 54.969 54.969 ↓ 0.0 0 1

Index Scan using pk_generic_statcast_fielding_outfield_pbp on statcast_fielding_outfield_pbp (cost=0.42..15,959.00 rows=1 width=69) (actual time=54.969..54.969 rows=0 loops=1)

  • Index Cond: (guid = '49671d5a-dec3-4307-9995-725a991d5114'::text)
15. 0.008 1,402.745 ↑ 1.0 1 1

Materialize (cost=0.42..19,973.76 rows=1 width=69) (actual time=606.362..1,402.745 rows=1 loops=1)

16. 1,402.737 1,402.737 ↑ 1.0 1 1

Index Scan using pk_generic_statcast_fielding_infield_pbp on statcast_fielding_infield_pbp (cost=0.42..19,973.76 rows=1 width=69) (actual time=606.354..1,402.737 rows=1 loops=1)

  • Index Cond: (guid = '49671d5a-dec3-4307-9995-725a991d5114'::text)
Planning time : 468.009 ms
Execution time : 1,473.015 ms