explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fFp6

Settings
# exclusive inclusive rows x rows loops node
1. 6.060 27.056 ↑ 950.5 2 1

GroupAggregate (cost=3,925.64..4,063.46 rows=1,901 width=230) (actual time=24.594..27.056 rows=2 loops=1)

  • Group Key: pitch.season, pitch.batter_mlb_id
2. 3.209 20.996 ↓ 2.5 4,819 1

Sort (cost=3,925.64..3,930.39 rows=1,901 width=53) (actual time=20.011..20.996 rows=4,819 loops=1)

  • Sort Key: pitch.batter_mlb_id
  • Sort Method: quicksort Memory: 645kB
3. 2.451 17.787 ↓ 2.5 4,819 1

Hash Left Join (cost=4.74..3,822.10 rows=1,901 width=53) (actual time=1.068..17.787 rows=4,819 loops=1)

  • Hash Cond: (pitch.pitch_result = mlbam_lk_pitch_code.pitch_code)
4. 2.103 15.321 ↓ 2.5 4,819 1

Hash Left Join (cost=3.15..3,802.29 rows=1,901 width=47) (actual time=1.044..15.321 rows=4,819 loops=1)

  • Hash Cond: (pitch.event_type = mlbam_lk_event_type.event_type)
5. 1.435 13.159 ↓ 2.5 4,819 1

Append (cost=0.00..3,785.98 rows=1,901 width=30) (actual time=0.967..13.159 rows=4,819 loops=1)

6. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((season IS NOT NULL) AND (batter_mlb_id IS NOT NULL) AND (batter_mlb_id = ANY ('{641355,669257}'::integer[])) AND (season = 2018))
7. 11.040 11.723 ↓ 2.5 4,819 1

Bitmap Heap Scan on aggregated_pitches_2018 pitch_1 (cost=60.34..3,785.98 rows=1,900 width=30) (actual time=0.964..11.723 rows=4,819 loops=1)

  • Recheck Cond: ((batter_mlb_id = ANY ('{641355,669257}'::integer[])) AND (batter_mlb_id IS NOT NULL))
  • Filter: ((season IS NOT NULL) AND (season = 2018))
  • Heap Blocks: exact=2148
8. 0.683 0.683 ↓ 2.5 4,819 1

Bitmap Index Scan on aggregated_pitches_2018_batter_mlb_id_idx (cost=0.00..59.87 rows=1,900 width=0) (actual time=0.683..0.683 rows=4,819 loops=1)

  • Index Cond: ((batter_mlb_id = ANY ('{641355,669257}'::integer[])) AND (batter_mlb_id IS NOT NULL))
9. 0.028 0.059 ↑ 1.0 51 1

Hash (cost=2.51..2.51 rows=51 width=41) (actual time=0.059..0.059 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
10. 0.031 0.031 ↑ 1.0 51 1

Seq Scan on mlbam_lk_event_type (cost=0.00..2.51 rows=51 width=41) (actual time=0.013..0.031 rows=51 loops=1)

11. 0.005 0.015 ↑ 1.2 12 1

Hash (cost=1.42..1.42 rows=14 width=10) (actual time=0.015..0.015 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.010 0.010 ↑ 1.2 12 1

Seq Scan on mlbam_lk_pitch_code (cost=0.00..1.42 rows=14 width=10) (actual time=0.003..0.010 rows=12 loops=1)

  • Filter: ((automatic = 0) AND (pitchout = 0) AND (bunt = 0))
  • Rows Removed by Filter: 12
Planning time : 6.554 ms
Execution time : 27.288 ms