explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ORb5

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 118,352.312 ↓ 9.0 9 1

GroupAggregate (cost=11,538,807.50..11,538,807.53 rows=1 width=23) (actual time=118,352.273..118,352.312 rows=9 loops=1)

  • Group Key: player.mlb_person_id, pm.pitch_type
2. 0.000 118,352.253 ↓ 119.0 119 1

Sort (cost=11,538,807.50..11,538,807.50 rows=1 width=37) (actual time=118,352.245..118,352.253 rows=119 loops=1)

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

Gather (cost=7,319,466.46..11,538,807.49 rows=1 width=37) (actual time=98,020.070..118,694.679 rows=119 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 923.582 118,122.664 ↓ 40.0 40 3 / 3

Parallel Hash Join (cost=7,318,466.46..11,537,807.39 rows=1 width=37) (actual time=93,340.801..118,122.664 rows=40 loops=3)

  • Hash Cond: ((game_1.game_id = pitch.game_id) AND (pitch_1.pitch_id = pitch.pitch_id))
5. 7,427.139 117,177.582 ↑ 2.4 7,362,338 3 / 3

Parallel Hash Left Join (cost=7,307,686.22..11,393,503.57 rows=17,803,142 width=54) (actual time=80,759.360..117,177.582 rows=7,362,338 loops=3)

  • Hash Cond: (pitch_1.pitch_id = tm.pitch_id)
  • Filter: ((sc.pitch_id IS NOT NULL) OR (he.pitch_id IS NOT NULL) OR (tm.pitch_id IS NOT NULL) OR (pitch_2.pitch_id IS NOT NULL))
  • Rows Removed by Filter: 6880175
6. 2,591.787 70,897.200 ↑ 1.3 14,242,513 3 / 3

Hash Left Join (cost=5,426,556.63..9,465,640.73 rows=17,803,142 width=94) (actual time=41,837.085..70,897.200 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.pitch_id = he.pitch_id)
7. 7,072.609 68,305.404 ↑ 1.3 14,242,513 3 / 3

Parallel Hash Left Join (cost=5,426,543.70..9,418,894.56 rows=17,803,142 width=70) (actual time=41,837.055..68,305.404 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.pitch_id = sc.pitch_id)
8. 5,165.098 60,474.344 ↑ 1.3 14,242,513 3 / 3

Parallel Hash Join (cost=4,890,365.16..8,835,982.77 rows=17,803,142 width=46) (actual time=41,073.189..60,474.344 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.game_id = game_1.game_id)
9. 6,223.698 55,267.652 ↑ 1.3 14,242,513 3 / 3

Parallel Hash Left Join (cost=4,871,838.71..8,770,722.43 rows=17,803,142 width=40) (actual time=41,030.812..55,267.652 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.pitch_id = pitch_2.pitch_id)
10. 8,134.444 8,134.444 ↑ 1.3 14,242,513 3 / 3

Parallel Seq Scan on pitch pitch_1 (cost=0.00..3,379,625.42 rows=17,803,142 width=20) (actual time=0.004..8,134.444 rows=14,242,513 loops=3)

11. 871.448 40,909.510 ↑ 12.7 1,402,867 3 / 3

Parallel Hash (cost=4,649,299.43..4,649,299.43 rows=17,803,142 width=20) (actual time=40,909.510..40,909.510 rows=1,402,867 loops=3)

  • Buckets: 67108864 Batches: 1 Memory Usage: 743232kB
12. 2,667.688 40,038.062 ↑ 12.7 1,402,867 3 / 3

Parallel Hash Join (cost=1,222,940.77..4,649,299.43 rows=17,803,142 width=20) (actual time=21,100.590..40,038.062 rows=1,402,867 loops=3)

  • Hash Cond: (pitch_2.synergy_event_id = syn.event_id)
13. 16,281.006 16,281.006 ↑ 1.3 14,242,513 3 / 3

Parallel Seq Scan on pitch pitch_2 (cost=0.00..3,379,625.42 rows=17,803,142 width=41) (actual time=0.351..16,281.006 rows=14,242,513 loops=3)

14. 671.090 21,089.368 ↑ 1.3 1,516,579 3 / 3

Parallel Hash (cost=1,199,238.12..1,199,238.12 rows=1,896,212 width=29) (actual time=21,089.368..21,089.368 rows=1,516,579 loops=3)

  • Buckets: 8388608 Batches: 1 Memory Usage: 350528kB
15. 20,418.278 20,418.278 ↑ 1.3 1,516,579 3 / 3

Parallel Seq Scan on event syn (cost=0.00..1,199,238.12 rows=1,896,212 width=29) (actual time=0.513..20,418.278 rows=1,516,579 loops=3)

16. 20.960 41.594 ↑ 1.2 103,551 3 / 3

Parallel Hash (cost=16,908.98..16,908.98 rows=129,398 width=6) (actual time=41.594..41.594 rows=103,551 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 16288kB
17. 20.634 20.634 ↑ 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.006..20.634 rows=103,551 loops=3)

18. 394.713 758.451 ↑ 1.3 1,218,122 3 / 3

Parallel Hash (cost=516,972.13..516,972.13 rows=1,536,513 width=24) (actual time=758.451..758.451 rows=1,218,122 loops=3)

  • Buckets: 4194304 Batches: 1 Memory Usage: 233088kB
19. 363.738 363.738 ↑ 1.3 1,218,122 3 / 3

Parallel Seq Scan on pitch_tracking_statcast sc (cost=0.00..516,972.13 rows=1,536,513 width=24) (actual time=1.229..363.738 rows=1,218,122 loops=3)

20. 0.001 0.009 ↓ 0.0 0 3 / 3

Hash (cost=11.30..11.30 rows=130 width=24) (actual time=0.009..0.009 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 0.008 0.008 ↓ 0.0 0 3 / 3

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

22. 2,519.728 38,853.243 ↑ 1.3 5,944,459 3 / 3

Parallel Hash (cost=1,788,225.93..1,788,225.93 rows=7,432,293 width=24) (actual time=38,853.243..38,853.243 rows=5,944,459 loops=3)

  • Buckets: 33554432 Batches: 1 Memory Usage: 1238272kB
23. 36,333.515 36,333.515 ↑ 1.3 5,944,459 3 / 3

Parallel Seq Scan on pitch_tracking_trackman tm (cost=0.00..1,788,225.93 rows=7,432,293 width=24) (actual time=0.682..36,333.515 rows=5,944,459 loops=3)

24. 3.367 21.500 ↓ 40.0 40 3 / 3

Parallel Hash (cost=10,780.23..10,780.23 rows=1 width=47) (actual time=21.500..21.500 rows=40 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
25. 0.011 18.133 ↓ 40.0 40 3 / 3

Nested Loop (cost=10,774.17..10,780.23 rows=1 width=47) (actual time=17.880..18.133 rows=40 loops=3)

26. 0.584 17.884 ↓ 40.0 40 3 / 3

Merge Join (cost=10,773.61..10,776.79 rows=1 width=28) (actual time=17.867..17.884 rows=40 loops=3)

  • Merge Cond: (pitch.game_id = game.game_id)
27. 2.346 17.289 ↓ 10.6 6,102 3 / 3

Sort (cost=10,651.07..10,652.50 rows=574 width=24) (actual time=16.733..17.289 rows=6,102 loops=3)

  • Sort Key: pitch.game_id
  • Sort Method: quicksort Memory: 806kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 1251kB
28. 1.101 14.943 ↓ 11.0 6,314 3 / 3

Nested Loop (cost=0.56..10,624.76 rows=574 width=24) (actual time=9.546..14.943 rows=6,314 loops=3)

29. 9.533 9.533 ↑ 1.0 1 3 / 3

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

  • Filter: (mlb_person_id = ANY ('{628317,621111}'::integer[]))
  • Rows Removed by Filter: 29210
30. 4.309 4.309 ↓ 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=24) (actual time=0.021..6.463 rows=9,470 loops=2)

  • Index Cond: (pitcher_id = player.player_id)
31. 0.003 0.011 ↑ 30.5 2 2 / 3

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

  • Sort Key: game.game_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
32. 0.007 0.007 ↑ 30.5 2 2 / 3

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

  • Index Cond: (game_date = '2019-10-03'::date)
33. 0.238 0.238 ↑ 1.0 1 119 / 3

Index Scan using pitch_metric_pkey on pitch_metric pm (cost=0.56..3.43 rows=1 width=19) (actual time=0.006..0.006 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 : 4.536 ms
Execution time : 118,695.059 ms