explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uQAf

Settings
# exclusive inclusive rows x rows loops node
1. 25.509 1,731,392.755 ↓ 30,819.0 30,819 1

Unique (cost=260,261.63..260,261.64 rows=1 width=2,354) (actual time=1,731,321.093..1,731,392.755 rows=30,819 loops=1)

2.          

CTE games

3. 0.932 707.219 ↓ 2.8 142 1

Unique (cost=10,638.78..10,639.04 rows=51 width=27) (actual time=704.497..707.219 rows=142 loops=1)

4. 2.329 706.287 ↓ 2.8 142 1

Sort (cost=10,638.78..10,638.91 rows=51 width=27) (actual time=704.496..706.287 rows=142 loops=1)

  • Sort Key: game_2.game_id
  • Sort Method: quicksort Memory: 36kB
5. 703.958 703.958 ↓ 2.8 142 1

Seq Scan on mlbam_pbp_pre_game game_2 (cost=0.00..10,637.34 rows=51 width=27) (actual time=594.581..703.958 rows=142 loops=1)

  • Filter: (date = '2019-08-17 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 264653
6. 721.606 1,731,367.246 ↓ 141,463.0 141,463 1

Sort (cost=249,622.59..249,622.59 rows=1 width=2,354) (actual time=1,731,321.090..1,731,367.246 rows=141,463 loops=1)

  • Sort Key: play_1.game_id, map_play.pbp_atbat_per_game, (COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint))
  • Sort Method: quicksort Memory: 267839kB
7. 2,565.132 1,730,645.640 ↓ 141,463.0 141,463 1

WindowAgg (cost=249,622.32..249,622.58 rows=1 width=2,354) (actual time=1,728,021.852..1,730,645.640 rows=141,463 loops=1)

8. 787.492 1,728,080.508 ↓ 141,463.0 141,463 1

Sort (cost=249,622.32..249,622.32 rows=1 width=2,907) (actual time=1,728,021.144..1,728,080.508 rows=141,463 loops=1)

  • Sort Key: play_1.game_id, (COALESCE(pitch_mlbam.top, CASE WHEN (pitch_bats.hitting_visiting_home = 'V'::text) THEN 'Y'::text ELSE 'N'::text END)), ((COALESCE((pitch_mlbam.pitch_per_game)::bigint, rank() OVER (?)))::integer)
  • Sort Method: quicksort Memory: 210402kB
9. 358.535 1,727,293.016 ↓ 141,463.0 141,463 1

WindowAgg (cost=249,622.28..249,622.31 rows=1 width=2,907) (actual time=1,726,885.971..1,727,293.016 rows=141,463 loops=1)

10. 635.797 1,726,934.481 ↓ 141,463.0 141,463 1

Sort (cost=249,622.28..249,622.29 rows=1 width=2,899) (actual time=1,726,885.903..1,726,934.481 rows=141,463 loops=1)

  • Sort Key: play_1.game_id, (COALESCE(pitch_mlbam.top, CASE WHEN (pitch_bats.hitting_visiting_home = 'V'::text) THEN 'Y'::text ELSE 'N'::text END)), map_play.pbp_atbat_per_game
  • Sort Method: quicksort Memory: 205805kB
11. 588.656 1,726,298.684 ↓ 141,463.0 141,463 1

WindowAgg (cost=249,622.23..249,622.27 rows=1 width=2,899) (actual time=1,725,326.335..1,726,298.684 rows=141,463 loops=1)

12. 331.003 1,725,710.028 ↓ 141,463.0 141,463 1

WindowAgg (cost=249,622.23..249,622.26 rows=1 width=2,891) (actual time=1,725,326.033..1,725,710.028 rows=141,463 loops=1)

13. 722.871 1,725,379.025 ↓ 141,463.0 141,463 1

Sort (cost=249,622.23..249,622.24 rows=1 width=2,883) (actual time=1,725,325.894..1,725,379.025 rows=141,463 loops=1)

  • Sort Key: play_1.game_id, play_1.rec_seq, (COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint)) DESC
  • Sort Method: quicksort Memory: 196998kB
14. 743.226 1,724,656.154 ↓ 141,463.0 141,463 1

WindowAgg (cost=249,622.19..249,622.22 rows=1 width=2,883) (actual time=1,723,845.883..1,724,656.154 rows=141,463 loops=1)

15. 893.135 1,723,912.928 ↓ 141,463.0 141,463 1

Sort (cost=249,622.19..249,622.19 rows=1 width=2,861) (actual time=1,723,845.709..1,723,912.928 rows=141,463 loops=1)

  • Sort Key: play_1.game_id, (COALESCE(pitch_mlbam.pitcher_id, play_1.pitcher_id)), pitch_mlbam.pitch_per_pitcher
  • Sort Method: quicksort Memory: 189016kB
16. 1,477.170 1,723,019.793 ↓ 141,463.0 141,463 1

WindowAgg (cost=249,622.11..249,622.18 rows=1 width=2,861) (actual time=1,721,473.458..1,723,019.793 rows=141,463 loops=1)

17. 939.172 1,721,542.623 ↓ 141,463.0 141,463 1

Sort (cost=249,622.11..249,622.11 rows=1 width=2,525) (actual time=1,721,473.299..1,721,542.623 rows=141,463 loops=1)

  • Sort Key: play_1.game_id, map_play.pbp_atbat_per_game, (COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint))
  • Sort Method: quicksort Memory: 166461kB
18. 2,741.730 1,720,603.451 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=246,505.61..249,622.10 rows=1 width=2,525) (actual time=1,714,725.982..1,720,603.451 rows=141,463 loops=1)

  • Join Filter: ((((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = lw_event.event_type) AND ((COALESCE(pitch_mlbam.event_type, play_1.event_type)) <> 'field_out'::text)) OR (((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = 'field_out'::text) AND (play.hit_trajectory = 'fly_ball'::text) AND (lw_event.event_type = 'flyout'::text)) OR (((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = 'field_out'::text) AND (play.hit_trajectory = 'ground_ball'::text) AND (lw_event.event_type = 'groundout'::text)) OR (((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = 'field_out'::text) AND (play.hit_trajectory = 'line_drive'::text) AND (lw_event.event_type = 'lineout'::text)) OR (((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = 'field_out'::text) AND (play.hit_trajectory = 'popup'::text) AND (lw_event.event_type = 'popout'::text)) OR (((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = 'field_out'::text) AND (play.hit_trajectory = 'bunt_grounder'::text) AND (lw_event.event_type = 'buntout'::text)) OR (((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = 'field_out'::text) AND (play.hit_trajectory = 'bunt_line_drive'::text) AND (lw_event.event_type = 'buntout'::text)) OR (((COALESCE(pitch_mlbam.event_type, play_1.event_type)) = 'field_out'::text) AND (play.hit_trajectory = 'bunt_popup'::text) AND (lw_event.event_type = 'buntout'::text)))
  • Rows Removed by Join Filter: 3680822
19. 916.466 1,715,598.313 ↓ 141,463.0 141,463 1

Hash Right Join (cost=246,492.98..249,606.80 rows=1 width=2,493) (actual time=1,714,725.464..1,715,598.313 rows=141,463 loops=1)

  • Hash Cond: ((count_values.balls = (COALESCE(pitch_mlbam.balls, (pitch_bats.balls)::bigint))) AND (count_values.strikes = (COALESCE(pitch_mlbam.strikes, (pitch_bats.strikes)::bigint))) AND (count_values.level = game.sport_code))
  • Join Filter: (count_values.year = CASE WHEN ((date_part('year'::text, (game_1.date)::timestamp without time zone)) > ((max((count_values.year)::integer) FILTER (WHERE (count_values.year <> 'LATEST'::text)) OVER (?)))::double precision) THEN 'LATEST'::text ELSE ((date_part('year'::text, (game_1.date)::timestamp without time zone)))::text END)
  • Rows Removed by Join Filter: 386906
20. 0.666 8.792 ↓ 1.4 288 1

WindowAgg (cost=0.00..3,109.34 rows=210 width=185) (actual time=8.330..8.792 rows=288 loops=1)

21. 8.126 8.126 ↓ 1.4 288 1

Seq Scan on count_values (cost=0.00..3,105.14 rows=210 width=41) (actual time=0.210..8.126 rows=288 loops=1)

  • Filter: ((pitcher_throws = 'all'::text) AND (batter_side = 'all'::text) AND (tto = 'all'::text) AND (base_out_state = 'all'::text))
  • Rows Removed by Filter: 16992
22. 414.524 1,714,673.055 ↓ 141,463.0 141,463 1

Hash (cost=246,492.96..246,492.96 rows=1 width=2,469) (actual time=1,714,673.055..1,714,673.055 rows=141,463 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 122911kB
23. 90.681 1,714,258.531 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=246,470.79..246,492.96 rows=1 width=2,469) (actual time=1,712,555.155..1,714,258.531 rows=141,463 loops=1)

24. 199.452 1,713,743.461 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=246,470.23..246,482.47 rows=1 width=2,385) (actual time=1,712,555.079..1,713,743.461 rows=141,463 loops=1)

25. 324.824 1,712,978.157 ↓ 141,463.0 141,463 1

Merge Left Join (cost=246,469.67..246,469.78 rows=1 width=2,297) (actual time=1,712,553.263..1,712,978.157 rows=141,463 loops=1)

  • Merge Cond: (((COALESCE(pitch_mlbam.balls, (pitch_bats.balls)::bigint)) = lw_pre_count.balls) AND ((COALESCE(pitch_mlbam.strikes, (pitch_bats.strikes)::bigint)) = lw_pre_count.strikes))
26. 3,555.909 1,712,631.981 ↓ 141,463.0 141,463 1

Sort (cost=246,462.08..246,462.09 rows=1 width=2,265) (actual time=1,712,551.979..1,712,631.981 rows=141,463 loops=1)

  • Sort Key: (COALESCE(pitch_mlbam.balls, (pitch_bats.balls)::bigint)), (COALESCE(pitch_mlbam.strikes, (pitch_bats.strikes)::bigint))
  • Sort Method: quicksort Memory: 160146kB
27. 3,800.592 1,709,076.072 ↓ 141,463.0 141,463 1

Hash Left Join (cost=244,598.28..246,462.07 rows=1 width=2,265) (actual time=14,385.955..1,709,076.072 rows=141,463 loops=1)

  • Hash Cond: ((CASE WHEN ((COALESCE(pitch_mlbam.pitch_result, CASE pitch_bats.pitch_result WHEN 'B'::text THEN 'B'::text WHEN 'T'::text THEN 'C'::text WHEN 'S'::text THEN 'S'::text WHEN 'F'::text THEN 'F'::text WHEN 'H'::text THEN 'X'::text ELSE NULL::text END)) = ANY ('{B,*B,I,P,V}'::text[])) THEN ((COALESCE(pitch_mlbam.balls, (pitch_bats.balls)::bigint)) + 1) WHEN ((COALESCE(pitch_mlbam.pitch_result, CASE pitch_bats.pitch_result WHEN 'B'::text THEN 'B'::text WHEN 'T'::text THEN 'C'::text WHEN 'S'::text THEN 'S'::text WHEN 'F'::text THEN 'F'::text WHEN 'H'::text THEN 'X'::text ELSE NULL::text END)) = ANY ('{D,E,H,J,X,Y,Z}'::text[])) THEN NULL::bigint ELSE (COALESCE(pitch_mlbam.balls, (pitch_bats.balls)::bigint)) END = lw_post_count.balls) AND (CASE WHEN (((COALESCE(pitch_mlbam.pitch_result, CASE pitch_bats.pitch_result WHEN 'B'::text THEN 'B'::text WHEN 'T'::text THEN 'C'::text WHEN 'S'::text THEN 'S'::text WHEN 'F'::text THEN 'F'::text WHEN 'H'::text THEN 'X'::text ELSE NULL::text END)) = ANY ('{A,C,K,L,M,O,Q,S,T,W}'::text[])) OR (((COALESCE(pitch_mlbam.pitch_result, CASE pitch_bats.pitch_result WHEN 'B'::text THEN 'B'::text WHEN 'T'::text THEN 'C'::text WHEN 'S'::text THEN 'S'::text WHEN 'F'::text THEN 'F'::text WHEN 'H'::text THEN 'X'::text ELSE NULL::text END)) = ANY ('{F,R}'::text[])) AND ((COALESCE(pitch_mlbam.strikes, (pitch_bats.strikes)::bigint)) < 2))) THEN ((COALESCE(pitch_mlbam.strikes, (pitch_bats.strikes)::bigint)) + 1) WHEN ((COALESCE(pitch_mlbam.pitch_result, CASE pitch_bats.pitch_result WHEN 'B'::text THEN 'B'::text WHEN 'T'::text THEN 'C'::text WHEN 'S'::text THEN 'S'::text WHEN 'F'::text THEN 'F'::text WHEN 'H'::text THEN 'X'::text ELSE NULL::text END)) = ANY ('{D,E,H,J,X,Y,Z}'::text[])) THEN NULL::bigint ELSE (COALESCE(pitch_mlbam.strikes, (pitch_bats.strikes)::bigint)) END = lw_post_count.strikes))
28. 1,823.378 1,705,273.706 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,590.73..246,454.48 rows=1 width=2,233) (actual time=14,384.107..1,705,273.706 rows=141,463 loops=1)

29. 1,714.615 1,680,391.859 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,590.17..246,446.04 rows=1 width=2,229) (actual time=14,381.950..1,680,391.859 rows=141,463 loops=1)

30. 1,963.968 1,666,087.037 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,589.76..246,437.90 rows=1 width=2,226) (actual time=14,381.208..1,666,087.037 rows=141,463 loops=1)

31. 1,346.872 1,646,015.805 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,589.34..246,429.77 rows=1 width=2,223) (actual time=14,379.456..1,646,015.805 rows=141,463 loops=1)

32. 1,489.063 1,632,503.115 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,588.92..246,421.62 rows=1 width=2,220) (actual time=14,377.999..1,632,503.115 rows=141,463 loops=1)

33. 1,824.992 1,616,301.900 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,588.51..246,413.51 rows=1 width=2,212) (actual time=14,376.695..1,616,301.900 rows=141,463 loops=1)

34. 1,291.835 1,613,911.056 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,587.96..246,405.21 rows=1 width=2,202) (actual time=14,376.671..1,613,911.056 rows=141,463 loops=1)

35. 1,422.535 1,611,204.591 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,587.40..246,396.93 rows=1 width=2,120) (actual time=14,376.652..1,611,204.591 rows=141,463 loops=1)

36. 1,549.847 1,608,225.963 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,586.84..246,388.64 rows=1 width=2,038) (actual time=14,376.630..1,608,225.963 rows=141,463 loops=1)

37. 7,456.074 1,599,037.114 ↓ 141,463.0 141,463 1

Nested Loop Left Join (cost=244,586.28..246,380.36 rows=1 width=1,956) (actual time=14,376.611..1,599,037.114 rows=141,463 loops=1)

  • Join Filter: ((statcast_guid.at_bat_number = map_play.pbp_atbat_per_game) AND (statcast_guid.pitch_number = (COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint))))
  • Rows Removed by Join Filter: 15920899
38. 1,453.112 1,527,177.424 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,585.85..245,430.09 rows=1 width=1,919) (actual time=14,376.534..1,527,177.424 rows=141,236 loops=1)

39. 1,768.765 1,520,357.344 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,585.30..245,421.81 rows=1 width=1,911) (actual time=14,376.502..1,520,357.344 rows=141,236 loops=1)

40. 2,076.544 1,499,098.011 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,584.75..245,413.44 rows=1 width=1,879) (actual time=14,374.594..1,499,098.011 rows=141,236 loops=1)

41. 1,792.056 1,458,464.039 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,584.19..245,404.98 rows=1 width=1,663) (actual time=14,372.942..1,458,464.039 rows=141,236 loops=1)

42. 1,664.509 1,435,769.055 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,583.63..245,396.61 rows=1 width=1,455) (actual time=14,370.382..1,435,769.055 rows=141,236 loops=1)

43. 1,446.813 1,414,190.270 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,583.07..245,388.04 rows=1 width=1,239) (actual time=14,367.178..1,414,190.270 rows=141,236 loops=1)

  • Join Filter: ((map.sv_game_mlb_id <> ''::text) AND (map.sv_sv_pitch_id <> ''::text))
44. 1,689.219 1,412,602.221 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,582.51..245,379.74 rows=1 width=1,231) (actual time=14,367.116..1,412,602.221 rows=141,236 loops=1)

  • Join Filter: ((map.tm_game_id IS NOT NULL) AND (map.tm_pitch_uid <> ''::text))
45. 1,561.332 1,358,514.446 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,581.94..245,371.42 rows=1 width=1,223) (actual time=14,360.542..1,358,514.446 rows=141,236 loops=1)

46. 1,314.588 1,321,502.878 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,581.39..245,363.14 rows=1 width=1,183) (actual time=14,357.580..1,321,502.878 rows=141,236 loops=1)

47. 1,270.116 1,290,387.494 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,580.82..245,354.84 rows=1 width=1,180) (actual time=14,357.427..1,290,387.494 rows=141,236 loops=1)

48. 1,492.715 1,288,976.142 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,580.53..245,346.84 rows=1 width=1,170) (actual time=14,357.405..1,288,976.142 rows=141,236 loops=1)

49. 2,085.870 1,287,200.955 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,579.98..245,338.55 rows=1 width=1,162) (actual time=14,357.376..1,287,200.955 rows=141,236 loops=1)

  • Join Filter: ((COALESCE(map.tm_game_id, ''::text) <> ''::text) AND (COALESCE(map.tm_pitch_uid, ''::text) <> ''::text))
50. 1,485.526 1,229,044.393 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,579.41..245,330.26 rows=1 width=961) (actual time=14,355.440..1,229,044.393 rows=141,236 loops=1)

  • Join Filter: ((COALESCE(map.sv_sv_pitch_id, ''::text) <> ''::text) AND (COALESCE(map.sv_game_mlb_id, ''::text) <> ''::text))
51. 2,367.134 1,227,276.395 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,578.86..245,321.97 rows=1 width=575) (actual time=14,355.381..1,227,276.395 rows=141,236 loops=1)

52. 10,910.550 1,193,131.161 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,578.29..245,294.72 rows=1 width=405) (actual time=14,352.062..1,193,131.161 rows=141,236 loops=1)

  • Join Filter: ((date_part('year'::text, (game_1.date)::timestamp without time zone)) = (team_home.season)::double precision)
  • Rows Removed by Join Filter: 6039851
53. 10,516.453 654,139.207 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=244,207.09..244,856.63 rows=1 width=399) (actual time=14,342.683..654,139.207 rows=141,236 loops=1)

  • Join Filter: ((date_part('year'::text, (game_1.date)::timestamp without time zone)) = (team_away.season)::double precision)
  • Rows Removed by Join Filter: 5930676
54. 1,120.358 102,971.346 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=243,835.89..244,418.54 rows=1 width=393) (actual time=14,316.869..102,971.346 rows=141,236 loops=1)

55. 1,292.478 91,117.052 ↓ 141,236.0 141,236 1

Nested Loop Left Join (cost=243,835.47..244,410.40 rows=1 width=382) (actual time=14,316.506..91,117.052 rows=141,236 loops=1)

56. 1,661.037 71,605.130 ↓ 141,236.0 141,236 1

Nested Loop (cost=243,835.05..244,402.26 rows=1 width=371) (actual time=14,315.113..71,605.130 rows=141,236 loops=1)

  • Join Filter: (games.game_id = game.game_id)
57. 1,866.211 54,266.897 ↓ 141,236.0 141,236 1

Nested Loop (cost=243,834.62..244,394.12 rows=1 width=401) (actual time=14,314.978..54,266.897 rows=141,236 loops=1)

  • Join Filter: (games.game_id = play.game_id)
58. 1,474.441 22,882.362 ↓ 70,618.0 141,236 1

Merge Join (cost=243,834.06..244,377.07 rows=2 width=295) (actual time=14,314.775..22,882.362 rows=141,236 loops=1)

  • Merge Cond: (play_1.game_id = games.game_id)
59. 5,852.586 20,955.894 ↓ 18.9 141,236 1

WindowAgg (cost=243,831.59..244,280.75 rows=7,486 width=265) (actual time=14,314.509..20,955.894 rows=141,236 loops=1)

60. 1,262.684 15,103.308 ↓ 18.9 141,236 1

Sort (cost=243,831.59..243,850.31 rows=7,486 width=179) (actual time=14,314.305..15,103.308 rows=141,236 loops=1)

  • Sort Key: play_1.game_id, play_1.rec_seq, pitch_bats.pitch_per_atbat
  • Sort Method: quicksort Memory: 43444kB
61. 297.647 13,840.624 ↓ 18.9 141,236 1

Nested Loop Left Join (cost=2.67..243,349.87 rows=7,486 width=179) (actual time=754.129..13,840.624 rows=141,236 loops=1)

  • Join Filter: ((game_1.sport_code = 'rok'::text) OR (game_1.valid_pitch_sequence_data = 'N'::text))
  • Rows Removed by Join Filter: 34629
  • Filter: (COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint) IS NOT NULL)
  • Rows Removed by Filter: 4843
62. 334.101 12,674.693 ↓ 19.2 144,714 1

Nested Loop (cost=2.11..184,202.87 rows=7,524 width=197) (actual time=713.751..12,674.693 rows=144,714 loops=1)

  • Join Filter: (games_1.game_id = map_play.pbp_game_id)
63. 159.726 5,786.940 ↓ 4.7 33,437 1

Nested Loop Left Join (cost=1.55..128,293.18 rows=7,049 width=222) (actual time=712.193..5,786.940 rows=33,437 loops=1)

  • Join Filter: ((game_1.sport_code <> 'rok'::text) AND (game_1.valid_pitch_sequence_data = 'Y'::text))
  • Rows Removed by Join Filter: 5207
64. 49.916 2,274.470 ↓ 1.6 11,482 1

Nested Loop (cost=0.98..20,068.70 rows=7,049 width=121) (actual time=707.547..2,274.470 rows=11,482 loops=1)

  • Join Filter: (games_1.game_id = play_1.game_id)
65. 2.242 735.826 ↓ 2.8 142 1

Nested Loop (cost=0.42..431.97 rows=51 width=69) (actual time=705.320..735.826 rows=142 loops=1)

66. 708.592 708.592 ↓ 2.8 142 1

CTE Scan on games games_1 (cost=0.00..1.02 rows=51 width=32) (actual time=704.500..708.592 rows=142 loops=1)

67. 24.992 24.992 ↑ 1.0 1 142

Index Scan using pk_staging_mlbam_pbp_pre_game on mlbam_pbp_pre_game game_1 (cost=0.42..8.44 rows=1 width=37) (actual time=0.145..0.176 rows=1 loops=142)

  • Index Cond: (game_id = games_1.game_id)
68. 1,488.728 1,488.728 ↑ 1.7 81 142

Index Scan using pk_staging_mlbam_pbp_play_by_play on mlbam_pbp_play_by_play play_1 (cost=0.56..383.31 rows=138 width=52) (actual time=0.415..10.484 rows=81 loops=142)

  • Index Cond: (game_id = game_1.game_id)
69. 3,352.744 3,352.744 ↑ 1.0 3 11,482

Index Scan using ix_staging_mlbam_pbp_pitch_by_pitch_game_id_rec_seq on mlbam_pbp_pitch_by_pitch pitch_mlbam (cost=0.56..15.31 rows=3 width=136) (actual time=0.182..0.292 rows=3 loops=11,482)

  • Index Cond: ((game_id = play_1.game_id) AND (rec_seq = play_1.rec_seq))
70. 6,553.652 6,553.652 ↓ 4.0 4 33,437

Index Scan using ixc_staging_map_pitch_pbp_game_id_pbp_rec_seq on map_pitch map_play (cost=0.56..7.92 rows=1 width=69) (actual time=0.104..0.196 rows=4 loops=33,437)

  • Index Cond: ((pbp_game_id = play_1.game_id) AND (pbp_rec_seq = play_1.rec_seq))
71. 868.284 868.284 ↓ 0.0 0 144,714

Index Scan using pk_staging_bats_manual on bats_manual pitch_bats (cost=0.56..7.84 rows=1 width=42) (actual time=0.003..0.006 rows=0 loops=144,714)

  • Index Cond: ((map_play.bats_date = date) AND (map_play.bats_away_team = away_team) AND (map_play.bats_home_team = home_team) AND (map_play.bats_doubleheader = doubleheader) AND (map_play.bats_inning = inning) AND (map_play.bats_hitting_visiting_home = hitting_visiting_home) AND (map_play.bats_batter_in_inning = batter_in_inning))
72. 451.993 452.027 ↓ 2,770.0 141,271 1

Sort (cost=2.47..2.59 rows=51 width=32) (actual time=0.222..452.027 rows=141,271 loops=1)

  • Sort Key: games.game_id
  • Sort Method: quicksort Memory: 36kB
73. 0.034 0.034 ↓ 2.8 142 1

CTE Scan on games (cost=0.00..1.02 rows=51 width=32) (actual time=0.003..0.034 rows=142 loops=1)

74. 29,518.324 29,518.324 ↑ 1.0 1 141,236

Index Scan using pk_staging_mlbam_pbp_play_by_play on mlbam_pbp_play_by_play play (cost=0.56..8.51 rows=1 width=106) (actual time=0.178..0.209 rows=1 loops=141,236)

  • Index Cond: ((game_id = play_1.game_id) AND (rec_seq = play_1.rec_seq))
75. 15,677.196 15,677.196 ↑ 1.0 1 141,236

Index Scan using pk_staging_mlbam_pbp_pre_game on mlbam_pbp_pre_game game (cost=0.42..8.13 rows=1 width=56) (actual time=0.100..0.111 rows=1 loops=141,236)

  • Index Cond: (game_id = play.game_id)
76. 18,219.444 18,219.444 ↑ 1.0 1 141,236

Index Scan using pk_staging_mlbam_daily_player_master on mlbam_daily_player_master batter (cost=0.42..8.13 rows=1 width=19) (actual time=0.127..0.129 rows=1 loops=141,236)

  • Index Cond: (player_id = play.batter_id)
77. 10,733.936 10,733.936 ↑ 1.0 1 141,236

Index Scan using pk_staging_mlbam_daily_player_master on mlbam_daily_player_master pitcher (cost=0.42..8.13 rows=1 width=19) (actual time=0.073..0.076 rows=1 loops=141,236)

  • Index Cond: (player_id = play.pitcher_id)
78. 149,003.980 540,651.408 ↓ 2.3 43 141,236

Bitmap Heap Scan on mlb_api_team_all_season team_away (cost=371.20..437.81 rows=19 width=14) (actual time=2.816..3.828 rows=43 loops=141,236)

  • Recheck Cond: (game.visiting_team_id = team_id)
  • Heap Blocks: exact=5954480
79. 391,647.428 391,647.428 ↓ 2.3 44 141,236

Bitmap Index Scan on pk_staging_mlb_api_team_all_season (cost=0.00..371.20 rows=19 width=0) (actual time=2.773..2.773 rows=44 loops=141,236)

  • Index Cond: (game.visiting_team_id = team_id)
80. 159,737.916 528,081.404 ↓ 2.3 44 141,236

Bitmap Heap Scan on mlb_api_team_all_season team_home (cost=371.20..437.81 rows=19 width=14) (actual time=2.650..3.739 rows=44 loops=141,236)

  • Recheck Cond: (game.home_team_id = team_id)
  • Heap Blocks: exact=6103815
81. 368,343.488 368,343.488 ↓ 2.3 44 141,236

Bitmap Index Scan on pk_staging_mlb_api_team_all_season (cost=0.00..371.20 rows=19 width=0) (actual time=2.608..2.608 rows=44 loops=141,236)

  • Index Cond: (game.home_team_id = team_id)
82. 31,778.100 31,778.100 ↑ 1.0 1 141,236

Index Scan using pk_staging_map_pitch_do_not_use on map_pitch map (cost=0.56..27.23 rows=1 width=233) (actual time=0.190..0.225 rows=1 loops=141,236)

  • Index Cond: ((play_1.game_id = pbp_game_id) AND (map_play.pbp_atbat_per_game = pbp_atbat_per_game))
  • Filter: ((((CASE WHEN ((game_1.sport_code <> 'rok'::text) AND (game_1.valid_pitch_sequence_data = 'Y'::text)) THEN 'mlbam'::text ELSE 'bats'::text END) = 'mlbam'::text) AND ((COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint)) = pbp_pitch_per_atbat)) OR (((CASE WHEN ((game_1.sport_code <> 'rok'::text) AND (game_1.valid_pitch_sequence_data = 'Y'::text)) THEN 'mlbam'::text ELSE 'bats'::text END) = 'bats'::text) AND ((COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint)) = bats_pitch_per_atbat)))
  • Rows Removed by Filter: 4
83. 282.472 282.472 ↓ 0.0 0 141,236

Index Scan using uq_staging_filtered_sportvision_game_mlb_id_sv_pitch_id on filtered_sportvision_fx_3 svfx (cost=0.56..8.27 rows=1 width=427) (actual time=0.002..0.002 rows=0 loops=141,236)

  • Index Cond: ((map.sv_game_mlb_id = game_mlb_id) AND (map.sv_sv_pitch_id = sv_pitch_id))
84. 56,070.692 56,070.692 ↑ 1.0 1 141,236

Index Scan using pk_staging_trackman on trackman (cost=0.56..8.28 rows=1 width=261) (actual time=0.337..0.397 rows=1 loops=141,236)

  • Index Cond: (map.tm_pitch_uid = (pitch_uid)::text)
  • Filter: (map.tm_game_id = game_id)
85. 282.472 282.472 ↓ 0.0 0 141,236

Index Scan using pk_staging_inside_edge_fx_pt_fieldloc on inside_edge_fx_pt_fieldloc ie (cost=0.56..8.27 rows=1 width=39) (actual time=0.002..0.002 rows=0 loops=141,236)

  • Index Cond: ((map.ie_pitch_and_event_id = pitch_and_event_id) AND (map.ie_game_id = game_id))
86. 141.236 141.236 ↓ 0.0 0 141,236

Index Scan using ix_sandlot_offsets on sandlot_offsets offsets (cost=0.29..7.99 rows=1 width=25) (actual time=0.001..0.001 rows=0 loops=141,236)

  • Index Cond: ((svfx.pitcher_id = pitcher_mlb_id) AND (svfx.pitch_type = pitch_type) AND (svfx.batter_stance = batter_stance))
87. 29,800.796 29,800.796 ↑ 1.0 1 141,236

Index Scan using pitch_class_pkey on pitch_class pc (cost=0.56..8.29 rows=1 width=40) (actual time=0.201..0.211 rows=1 loops=141,236)

  • Index Cond: (map.tm_pitch_uid = pitch_uid)
88. 35,450.236 35,450.236 ↓ 0.0 0 141,236

Index Scan using secret_framing_pk on framing (cost=0.56..8.27 rows=1 width=77) (actual time=0.251..0.251 rows=0 loops=141,236)

  • Index Cond: (map.tm_pitch_uid = pitch_uid)
89. 52,398.556 52,398.556 ↓ 0.0 0 141,236

Index Scan using qc_flavored_tm_pkey on qc_flavored_tm (cost=0.56..8.30 rows=1 width=68) (actual time=0.355..0.371 rows=0 loops=141,236)

  • Index Cond: ((map.tm_pitch_uid = pitch_uid) AND (situational_effect = 'N'::text) AND (horizontal_effect = 'N'::text) AND (speed_effect = 'N'::text))
  • Filter: ((bad_data_flag = 0) AND (map.tm_game_id = game_id))
  • Rows Removed by Filter: 0
90. 141.236 141.236 ↓ 0.0 0 141,236

Index Scan using qc_flavored_sv_pkey on qc_flavored_sv (cost=0.56..8.29 rows=1 width=49) (actual time=0.001..0.001 rows=0 loops=141,236)

  • Index Cond: ((map.sv_game_mlb_id = game_mlb_id) AND (map.sv_sv_pitch_id = sv_pitch_id) AND (situational_effect = 'N'::text) AND (horizontal_effect = 'N'::text) AND (speed_effect = 'N'::text))
  • Filter: (bad_data_flag = 0)
91. 19,914.276 19,914.276 ↓ 0.0 0 141,236

Index Scan using sandlot_value_pbp_v3_2_upsert_pbp_identifiers on sandlot_value_pbp_v3_2_upsert valpred (cost=0.56..8.56 rows=1 width=251) (actual time=0.141..0.141 rows=0 loops=141,236)

  • Index Cond: ((play_1.game_id = game_id) AND (map_play.pbp_atbat_per_game = atbat_per_game) AND ((COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint)) = pitch_per_atbat))
92. 20,902.928 20,902.928 ↓ 0.0 0 141,236

Index Scan using pk_secret_pitch_value_v5_0 on pitch_value_v5_0 valpred5 (cost=0.56..8.36 rows=1 width=243) (actual time=0.142..0.148 rows=0 loops=141,236)

  • Index Cond: ((play_1.game_id = game_id) AND (map_play.pbp_atbat_per_game = atbat_per_game) AND ((COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint)) = pitch_per_atbat))
93. 38,557.428 38,557.428 ↑ 1.0 1 141,236

Index Scan using secret_pk_secret_value_v5_0_milb on pitch_value_v5_0_milb valpred5_milb (cost=0.56..8.45 rows=1 width=251) (actual time=0.248..0.273 rows=1 loops=141,236)

  • Index Cond: ((play_1.game_id = game_id) AND (map_play.pbp_atbat_per_game = atbat_per_game) AND ((COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint)) = pitch_per_atbat))
94. 19,490.568 19,490.568 ↓ 0.0 0 141,236

Index Scan using pk_generic_exp_bip_outcomes on exp_bip_outcomes exp_bip (cost=0.55..8.36 rows=1 width=67) (actual time=0.137..0.138 rows=0 loops=141,236)

  • Index Cond: ((play_1.game_id = game_id) AND (map_play.pbp_atbat_per_game = atbat_per_game) AND ((COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint)) = pitch_per_atbat) AND (speed_effect = 'Y'::text) AND (horiz_effect = 'Y'::text))
95. 5,366.968 5,366.968 ↓ 0.0 0 141,236

Index Scan using pk_staging_statcast_segment_bearing on statcast_segment_bearing sc_seg (cost=0.55..8.27 rows=1 width=45) (actual time=0.038..0.038 rows=0 loops=141,236)

  • Index Cond: (map.sc_play_guid = guid)
96. 64,403.616 64,403.616 ↑ 6.7 113 141,236

Index Scan using ix_staging_statcast_game_pk on statcast_guid (cost=0.43..938.98 rows=753 width=49) (actual time=0.123..0.456 rows=113 loops=141,236)

  • Index Cond: (game_pk = game.game_pk)
97. 7,639.002 7,639.002 ↓ 0.0 0 141,463

Index Scan using pk_staging_statcast_guid_video on statcast_guid_video video_broadcast (cost=0.56..8.27 rows=1 width=119) (actual time=0.054..0.054 rows=0 loops=141,463)

  • Index Cond: ((guid = COALESCE(map.sc_play_guid, statcast_guid.guid)) AND (media_source_type = 'BROADCAST'::text))
98. 1,556.093 1,556.093 ↓ 0.0 0 141,463

Index Scan using pk_staging_statcast_guid_video on statcast_guid_video video_high_3b (cost=0.56..8.27 rows=1 width=119) (actual time=0.011..0.011 rows=0 loops=141,463)

  • Index Cond: ((guid = COALESCE(map.sc_play_guid, statcast_guid.guid)) AND (media_source_type = 'CH_HIGH_SIDEVIEW'::text))
99. 1,414.630 1,414.630 ↓ 0.0 0 141,463

Index Scan using pk_staging_statcast_guid_video on statcast_guid_video video_pitchcast (cost=0.56..8.27 rows=1 width=119) (actual time=0.009..0.010 rows=0 loops=141,463)

  • Index Cond: ((guid = COALESCE(map.sc_play_guid, statcast_guid.guid)) AND (media_source_type = 'PITCHCAST'::text))
100. 565.852 565.852 ↓ 0.0 0 141,463

Index Scan using pk_staging_bats_manual on bats_manual bats (cost=0.56..8.29 rows=1 width=78) (actual time=0.004..0.004 rows=0 loops=141,463)

  • Index Cond: ((map.bats_date = date) AND (map.bats_away_team = away_team) AND (map.bats_home_team = home_team) AND (map.bats_doubleheader = doubleheader) AND (map.bats_inning = inning) AND (map.bats_hitting_visiting_home = hitting_visiting_home) AND (map.bats_batter_in_inning = batter_in_inning) AND (map.bats_pitch_per_atbat = pitch_per_atbat))
101. 14,712.152 14,712.152 ↓ 0.0 0 141,463

Index Scan using jg_pitch_type_pk on jg_pitch_type jg (cost=0.41..8.10 rows=1 width=67) (actual time=0.104..0.104 rows=0 loops=141,463)

  • Index Cond: ((map.tm_game_id = game_id) AND (map.tm_pitch_uid = pitch_uid))
102. 12,165.818 12,165.818 ↓ 0.0 0 141,463

Index Scan using pitch_class_ops_pkey on pitch_class_ops pc_ops (cost=0.42..8.13 rows=1 width=40) (actual time=0.086..0.086 rows=0 loops=141,463)

  • Index Cond: (map.tm_pitch_uid = pitch_uid)
103. 18,107.264 18,107.264 ↑ 1.0 1 141,463

Index Scan using pk_staging_mlbam_daily_team_history on mlbam_daily_team_history away (cost=0.41..8.13 rows=1 width=13) (actual time=0.127..0.128 rows=1 loops=141,463)

  • Index Cond: ((team_id = game.visiting_team_id) AND (year = (date_part('year'::text, (game_1.date)::timestamp without time zone))))
104. 12,590.207 12,590.207 ↑ 1.0 1 141,463

Index Scan using pk_staging_mlbam_daily_team_history on mlbam_daily_team_history home (cost=0.41..8.13 rows=1 width=13) (actual time=0.089..0.089 rows=1 loops=141,463)

  • Index Cond: ((team_id = game.home_team_id) AND (year = (date_part('year'::text, (game_1.date)::timestamp without time zone))))
105. 23,058.469 23,058.469 ↑ 1.0 1 141,463

Index Scan using pitch_media_game_id_at_bat_num_pitch_num_idx on pitch_media (cost=0.56..8.43 rows=1 width=35) (actual time=0.161..0.163 rows=1 loops=141,463)

  • Index Cond: ((game_id = play_1.game_id) AND (at_bat_num = map_play.pbp_atbat_per_game) AND (pitch_num = (COALESCE(pitch_mlbam.pitch_per_atbat, (pitch_bats.pitch_per_atbat)::bigint))))
106. 0.015 1.774 ↑ 1.0 12 1

Hash (cost=7.38..7.38 rows=12 width=48) (actual time=1.774..1.774 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
107. 0.024 1.759 ↑ 1.0 12 1

Subquery Scan on lw_post_count (cost=7.04..7.38 rows=12 width=48) (actual time=1.717..1.759 rows=12 loops=1)

108. 0.152 1.735 ↑ 1.0 12 1

HashAggregate (cost=7.04..7.25 rows=12 width=48) (actual time=1.714..1.735 rows=12 loops=1)

  • Group Key: lw_count.balls, lw_count.strikes
109. 1.583 1.583 ↑ 1.0 48 1

Seq Scan on lw_count (cost=0.00..6.33 rows=48 width=37) (actual time=1.154..1.583 rows=48 loops=1)

  • Filter: (season > '2015'::double precision)
  • Rows Removed by Filter: 138
110. 20.239 21.352 ↓ 10,964.5 131,574 1

Sort (cost=7.59..7.62 rows=12 width=48) (actual time=1.162..21.352 rows=131,574 loops=1)

  • Sort Key: lw_pre_count.balls, lw_pre_count.strikes
  • Sort Method: quicksort Memory: 25kB
111. 0.019 1.113 ↑ 1.0 12 1

Subquery Scan on lw_pre_count (cost=7.04..7.38 rows=12 width=48) (actual time=1.099..1.113 rows=12 loops=1)

112. 0.164 1.094 ↑ 1.0 12 1

HashAggregate (cost=7.04..7.25 rows=12 width=48) (actual time=1.081..1.094 rows=12 loops=1)

  • Group Key: lw_count_1.balls, lw_count_1.strikes
113. 0.930 0.930 ↑ 1.0 48 1

Seq Scan on lw_count lw_count_1 (cost=0.00..6.33 rows=48 width=37) (actual time=0.496..0.930 rows=48 loops=1)

  • Filter: (season > '2015'::double precision)
  • Rows Removed by Filter: 138
114. 565.852 565.852 ↑ 3.0 1 141,463

Index Scan using pk_video_media on media media_sd (cost=0.56..12.66 rows=3 width=92) (actual time=0.004..0.004 rows=1 loops=141,463)

  • Index Cond: ((pitch_media_event_id = pitch_media.event_id) AND (type = 'FLASH_1200K_640X360'::text))
115. 424.389 424.389 ↓ 0.0 0 141,463

Index Scan using pk_video_media on media media_hd (cost=0.56..10.47 rows=2 width=92) (actual time=0.003..0.003 rows=0 loops=141,463)

  • Index Cond: ((pitch_media_event_id = pitch_media.event_id) AND (type = 'FLASH_4500K_1280X720'::text))
116. 2,263.246 2,263.408 ↑ 1.0 27 141,463

HashAggregate (cost=12.63..13.12 rows=28 width=43) (actual time=0.001..0.016 rows=27 loops=141,463)

  • Group Key: lw_event.event_type
117. 0.162 0.162 ↑ 1.0 156 1

Seq Scan on lw_event (cost=0.00..11.07 rows=156 width=29) (actual time=0.073..0.162 rows=156 loops=1)

  • Filter: (season > '2013'::double precision)
  • Rows Removed by Filter: 250
Planning time : 690.858 ms
Execution time : 1,731,530.455 ms