explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tS0Ga

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 245,722.505 ↓ 9.0 9 1

GroupAggregate (cost=20,734,201.11..20,734,201.14 rows=1 width=23) (actual time=245,722.481..245,722.505 rows=9 loops=1)

  • Group Key: player.mlb_person_id, pm.pitch_type
  • (tm.pitch_id = pitch_1.pitch_id)))
2. 0.062 245,722.469 ↓ 119.0 119 1

Sort (cost=20,734,201.11..20,734,201.11 rows=1 width=37) (actual time=245,722.463..245,722.469 rows=119 loops=1)

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

Hash Join (cost=7,352,799.14..20,734,201.10 rows=1 width=37) (actual time=243,648.029..245,722.407 rows=119 loops=1)

  • Hash Cond: (COALESCE(sc.pitch_id, tm.pitch_id, he.pitch_id, pitch_1.pitch_id) = pitch.pitch_id)
4. 7,177.796 243,780.783 ↓ 6.0 22,065,500 1

Hash Right Join (cost=7,341,048.17..20,708,621.50 rows=3,687,631 width=94) (actual time=191,506.324..243,780.783 rows=22,065,500 loops=1)

  • Hash Cond: (pitch_1.game_id = game_1.game_id)
  • Join Filter: (((sc.pitch_id IS NULL) OR (sc.pitch_id = pitch_1.pitch_id)) AND ((he.pitch_id IS NULL) OR (he.pitch_id = pitch_1.pitch_id)) AND ((tm.pitch_id IS NULL) OR
  • Rows Removed by Join Filter: 28048
  • Filter: ((sc.pitch_id IS NOT NULL) OR (he.pitch_id IS NOT NULL) OR (tm.pitch_id IS NOT NULL) OR (pitch_1.pitch_id IS NOT NULL))
  • Rows Removed by Filter: 232764
5. 6,391.948 84,342.687 ↑ 10.2 4,208,601 1

Hash Join (cost=1,282,671.43..5,023,700.63 rows=42,727,540 width=24) (actual time=39,242.478..84,342.687 rows=4,208,601 loops=1)

  • Hash Cond: (pitch_1.synergy_event_id = syn.event_id)
6. 60,503.108 60,503.108 ↑ 1.0 42,727,539 1

Seq Scan on pitch pitch_1 (cost=0.00..3,628,869.40 rows=42,727,540 width=45) (actual time=0.025..60,503.108 rows=42,727,539 loops=1)

7. 1,413.431 17,447.631 ↑ 1.0 4,549,736 1

Hash (cost=1,225,785.08..1,225,785.08 rows=4,550,908 width=29) (actual time=17,447.630..17,447.631 rows=4,549,736 loops=1)

  • Buckets: 8388608 Batches: 1 Memory Usage: 329802kB
8. 16,034.200 16,034.200 ↑ 1.0 4,549,736 1

Seq Scan on event syn (cost=0.00..1,225,785.08 rows=4,550,908 width=29) (actual time=0.022..16,034.200 rows=4,549,736 loops=1)

9. 5,281.046 152,260.300 ↓ 4.9 18,104,179 1

Hash (cost=6,012,281.35..6,012,281.35 rows=3,687,631 width=78) (actual time=152,260.300..152,260.300 rows=18,104,179 loops=1)

  • Buckets: 33554432 (originally 4194304) Batches: 1 (originally 1) Memory Usage: 1471385kB
10. 114,549.248 146,979.254 ↓ 4.9 18,104,179 1

Hash Right Join (cost=630,722.19..6,012,281.35 rows=3,687,631 width=78) (actual time=8,710.640..146,979.254 rows=18,104,179 loops=1)

  • Hash Cond: (tm.game_id = game_1.game_id)
  • Join Filter: (((sc.pitch_id IS NULL) OR (sc.pitch_id = tm.pitch_id)) AND ((he.pitch_id IS NULL) OR (he.pitch_id = tm.pitch_id)))
  • Rows Removed by Join Filter: 1077856827
11. 23,722.833 23,722.833 ↑ 1.0 17,833,377 1

Seq Scan on pitch_tracking_trackman tm (cost=0.00..1,892,278.04 rows=17,837,504 width=28) (actual time=0.018..23,722.833 rows=17,833,377 loops=1)

12. 601.275 8,707.173 ↓ 1.1 3,952,472 1

Hash (cost=584,626.80..584,626.80 rows=3,687,631 width=54) (actual time=8,707.173..8,707.173 rows=3,952,472 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 272811kB
13. 569.500 8,105.898 ↓ 1.1 3,952,472 1

Hash Left Join (cost=22,615.41..584,626.80 rows=3,687,631 width=54) (actual time=324.743..8,105.898 rows=3,952,472 loops=1)

  • Hash Cond: (game_1.game_id = he.game_id)
  • Join Filter: ((sc.pitch_id IS NULL) OR (sc.pitch_id = he.pitch_id))
14. 1,008.653 7,536.395 ↓ 1.1 3,952,472 1

Hash Right Join (cost=22,602.49..570,765.96 rows=3,687,631 width=30) (actual time=324.730..7,536.395 rows=3,952,472 loops=1)

  • Hash Cond: (sc.game_id = game_1.game_id)
15. 6,225.642 6,225.642 ↑ 1.0 3,654,366 1

Seq Scan on pitch_tracking_statcast sc (cost=0.00..538,483.31 rows=3,687,631 width=28) (actual time=22.206..6,225.642 rows=3,654,366 loops=1)

16. 61.042 302.100 ↓ 1.0 310,653 1

Hash (cost=18,720.55..18,720.55 rows=310,555 width=6) (actual time=302.100..302.100 rows=310,653 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 16231kB
17. 241.058 241.058 ↓ 1.0 310,653 1

Seq Scan on game game_1 (cost=0.00..18,720.55 rows=310,555 width=6) (actual time=0.557..241.058 rows=310,653 loops=1)

18. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=11.30..11.30 rows=130 width=28) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.002 0.002 ↓ 0.0 0 1

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

20. 0.049 84.829 ↓ 119.0 119 1

Hash (cost=11,750.96..11,750.96 rows=1 width=23) (actual time=84.829..84.829 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
21. 0.000 84.780 ↓ 119.0 119 1

Nested Loop (cost=1,122.62..11,750.96 rows=1 width=23) (actual time=30.141..84.780 rows=119 loops=1)

22. 1.968 77.324 ↓ 119.0 119 1

Gather (cost=1,122.06..11,747.86 rows=1 width=20) (actual time=29.639..77.324 rows=119 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
23. 0.854 75.356 ↓ 60.0 60 2 / 2

Hash Join (cost=122.06..10,747.76 rows=1 width=20) (actual time=23.658..75.356 rows=60 loops=2)

  • Hash Cond: (pitch.game_id = game.game_id)
24. 1.326 73.465 ↓ 16.5 9,470 2 / 2

Nested Loop (cost=0.56..10,624.76 rows=574 width=24) (actual time=16.076..73.465 rows=9,470 loops=2)

25. 17.016 17.016 ↑ 1.0 1 2 / 2

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

  • Filter: (mlb_person_id = ANY ('{628317,621111}'::integer[]))
  • Rows Removed by Filter: 43815
26. 55.123 55.123 ↓ 2.3 9,470 2 / 2

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

  • Index Cond: (pitcher_id = player.player_id)
27. 0.004 1.037 ↑ 30.5 2 2 / 2

Hash (cost=120.73..120.73 rows=61 width=4) (actual time=1.037..1.037 rows=2 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 1.033 1.033 ↑ 30.5 2 2 / 2

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

  • Index Cond: (game_date = '2019-10-03'::date)
29. 7.497 7.497 ↑ 1.0 1 119

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

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