explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bjt5

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 121.003 ↓ 9.0 9 1

GroupAggregate (cost=126,749.72..126,749.75 rows=1 width=23) (actual time=120.979..121.003 rows=9 loops=1)

  • Group Key: player.mlb_person_id, pm.pitch_type
2. 0.053 120.972 ↓ 119.0 119 1

Sort (cost=126,749.72..126,749.72 rows=1 width=37) (actual time=120.967..120.972 rows=119 loops=1)

  • Sort Key: player.mlb_person_id, pm.pitch_type
  • Sort Method: quicksort Memory: 34kB
3. 0.000 120.919 ↓ 119.0 119 1

Nested Loop (cost=114,723.75..126,749.71 rows=1 width=37) (actual time=117.474..120.919 rows=119 loops=1)

4. 15.750 121.063 ↓ 119.0 119 1

Gather (cost=114,723.19..126,746.27 rows=1 width=66) (actual time=117.460..121.063 rows=119 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.264 105.313 ↓ 40.0 40 3 / 3

Parallel Hash Join (cost=113,723.19..125,746.17 rows=1 width=66) (actual time=104.349..105.313 rows=40 loops=3)

  • Hash Cond: (pitch_1.pitch_id = pitch.pitch_id)
6. 0.020 83.158 ↑ 16.8 208 3 / 3

Nested Loop Left Join (cost=103,091.25..115,101.11 rows=3,497 width=46) (actual time=82.256..83.158 rows=208 loops=3)

7. 0.033 82.306 ↑ 16.8 208 3 / 3

Merge Left Join (cost=103,090.69..103,108.83 rows=3,497 width=38) (actual time=82.252..82.306 rows=208 loops=3)

  • Merge Cond: (pitch_1.pitch_id = he.pitch_id)
8. 0.121 82.270 ↑ 16.8 208 3 / 3

Sort (cost=103,074.82..103,083.56 rows=3,497 width=30) (actual time=82.247..82.270 rows=208 loops=3)

  • Sort Key: pitch_1.pitch_id
  • Sort Method: quicksort Memory: 73kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
9. 0.000 82.149 ↑ 16.8 208 3 / 3

Nested Loop Left Join (cost=28,020.47..102,868.99 rows=3,497 width=30) (actual time=80.192..82.149 rows=208 loops=3)

10. 0.006 81.330 ↑ 16.8 208 3 / 3

Nested Loop Left Join (cost=28,020.04..91,388.20 rows=3,497 width=22) (actual time=80.187..81.330 rows=208 loops=3)

11. 0.028 80.284 ↑ 16.8 208 3 / 3

Nested Loop (cost=28,018.91..67,295.44 rows=3,497 width=18) (actual time=80.181..80.284 rows=208 loops=3)

12. 7.874 80.174 ↑ 25.0 1 3 / 3

Merge Join (cost=28,018.35..28,665.89 rows=25 width=10) (actual time=80.172..80.174 rows=1 loops=3)

  • Merge Cond: (game_1.game_id = game.game_id)
13. 44.303 72.274 ↑ 1.9 68,980 3 / 3

Sort (cost=27,895.81..28,219.31 rows=129,398 width=6) (actual time=64.634..72.274 rows=68,980 loops=3)

  • Sort Key: game_1.game_id
  • Sort Method: quicksort Memory: 7983kB
  • Worker 0: Sort Method: quicksort Memory: 8450kB
  • Worker 1: Sort Method: quicksort Memory: 7346kB
14. 27.971 27.971 ↑ 1.2 103,551 3 / 3

Parallel Seq Scan on game game_1 (cost=0.00..16,908.98 rows=129,398 width=6) (actual time=0.005..27.971 rows=103,551 loops=3)

15. 0.008 0.026 ↑ 30.5 2 3 / 3

Sort (cost=122.54..122.69 rows=61 width=4) (actual time=0.025..0.026 rows=2 loops=3)

  • Sort Key: game.game_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
16. 0.018 0.018 ↑ 30.5 2 3 / 3

Index Scan using game_game_date_idx on game (cost=0.42..120.73 rows=61 width=4) (actual time=0.017..0.018 rows=2 loops=3)

  • Index Cond: (game_date = '2019-10-03'::date)
17. 0.082 0.082 ↑ 3.6 312 2 / 3

Index Scan using pitch_game_id_idx on pitch pitch_1 (cost=0.56..1,534.05 rows=1,113 width=20) (actual time=0.017..0.123 rows=312 loops=2)

  • Index Cond: (game_id = game_1.game_id)
18. 0.208 1.040 ↓ 0.0 0 624 / 3

Nested Loop (cost=1.12..6.88 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=624)

19. 0.832 0.832 ↑ 1.0 1 624 / 3

Index Scan using pitch_pkey on pitch pitch_2 (cost=0.56..3.46 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=624)

  • Index Cond: (pitch_1.pitch_id = pitch_id)
20. 0.000 0.000 ↓ 0.0 0 624 / 3

Index Scan using event_pkey on event syn (cost=0.56..3.42 rows=1 width=29) (actual time=0.000..0.000 rows=0 loops=624)

  • Index Cond: (event_id = pitch_2.synergy_event_id)
21. 0.832 0.832 ↑ 1.0 1 624 / 3

Index Scan using pitch_tracking_statcast_pkey on pitch_tracking_statcast sc (cost=0.43..3.28 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=624)

  • Index Cond: (pitch_1.pitch_id = pitch_id)
22. 0.002 0.003 ↓ 0.0 0 1 / 3

Sort (cost=15.86..16.19 rows=130 width=24) (actual time=0.010..0.010 rows=0 loops=1)

  • Sort Key: he.pitch_id
  • Sort Method: quicksort Memory: 25kB
23. 0.002 0.002 ↓ 0.0 0 1 / 3

Seq Scan on pitch_tracking_hawkeye he (cost=0.00..11.30 rows=130 width=24) (actual time=0.005..0.005 rows=0 loops=1)

24. 0.832 0.832 ↑ 1.0 1 624 / 3

Index Scan using pitch_tracking_trackman_pkey on pitch_tracking_trackman tm (cost=0.56..3.43 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=624)

  • Index Cond: (pitch_1.pitch_id = pitch_id)
25. 8.792 21.891 ↓ 11.0 6,314 3 / 3

Parallel Hash (cost=10,624.76..10,624.76 rows=574 width=20) (actual time=21.891..21.891 rows=6,314 loops=3)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1560kB
26. 0.784 13.099 ↓ 11.0 6,314 3 / 3

Nested Loop (cost=0.56..10,624.76 rows=574 width=20) (actual time=8.735..13.099 rows=6,314 loops=3)

27. 8.721 8.721 ↑ 1.0 1 3 / 3

Parallel Seq Scan on player (cost=0.00..3,371.35 rows=1 width=8) (actual time=8.716..8.721 rows=1 loops=3)

  • Filter: (mlb_person_id = ANY ('{628317,621111}'::integer[]))
  • Rows Removed by Filter: 29210
28. 3.594 3.594 ↓ 2.3 9,470 2 / 3

Index Scan using pitch_pitcher_id_idx on pitch (cost=0.56..7,212.84 rows=4,057 width=20) (actual time=0.024..5.391 rows=9,470 loops=2)

  • Index Cond: (pitcher_id = player.player_id)
29. 0.476 0.476 ↑ 1.0 1 119

Index Scan using pitch_metric_pkey on pitch_metric pm (cost=0.56..3.43 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=119)

  • Index Cond: (pitch_id = pitch_1.pitch_id)
  • Filter: (pitch_type = ANY ('{FB,SI,CB,SL,CH,CT,KN}'::text[]))
Planning time : 4.167 ms
Execution time : 121.846 ms