explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bfEv

Settings
# exclusive inclusive rows x rows loops node
1. 484.595 238,018.090 ↓ 119.0 119 1

Gather (cost=4,764,075.65..8,916,654.89 rows=1 width=23) (actual time=181,503.102..238,018.090 rows=119 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 1,437.427 237,533.495 ↓ 40.0 40 3 / 3

Parallel Hash Join (cost=4,763,075.65..8,915,654.79 rows=1 width=23) (actual time=201,269.099..237,533.495 rows=40 loops=3)

  • Hash Cond: (COALESCE(sc.pitch_id, tm.pitch_id, he.pitch_id, pitch_2.pitch_id) = pitch.pitch_id)
3. 7,668.997 236,079.180 ↑ 1.3 14,242,513 3 / 3

Parallel Hash Left Join (cost=4,752,324.44..8,838,141.79 rows=17,803,142 width=64) (actual time=174,464.753..236,079.180 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.pitch_id = tm.pitch_id)
4. 2,261.993 160,174.988 ↑ 1.3 14,242,513 3 / 3

Hash Left Join (cost=4,159,803.76..8,198,887.86 rows=17,803,142 width=64) (actual time=106,168.230..160,174.988 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.pitch_id = he.pitch_id)
5. 6,624.911 157,912.982 ↑ 1.3 14,242,513 3 / 3

Parallel Hash Left Join (cost=4,159,790.83..8,152,141.69 rows=17,803,142 width=48) (actual time=106,168.196..157,912.982 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.pitch_id = sc.pitch_id)
6. 4,430.215 129,143.439 ↑ 1.3 14,242,513 3 / 3

Parallel Hash Join (cost=4,028,746.71..7,974,364.32 rows=17,803,142 width=32) (actual time=84,018.131..129,143.439 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.game_id = game_1.game_id)
7. 5,617.771 124,393.429 ↑ 1.3 14,242,513 3 / 3

Parallel Hash Left Join (cost=4,019,182.05..7,918,065.78 rows=17,803,142 width=36) (actual time=83,697.542..124,393.429 rows=14,242,513 loops=3)

  • Hash Cond: (pitch_1.pitch_id = pitch_2.pitch_id)
8. 35,201.255 35,201.255 ↑ 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.011..35,201.255 rows=14,242,513 loops=3)

9. 894.583 83,574.403 ↑ 12.7 1,402,867 3 / 3

Parallel Hash (cost=3,796,642.78..3,796,642.78 rows=17,803,142 width=16) (actual time=83,574.403..83,574.403 rows=1,402,867 loops=3)

  • Buckets: 67108864 Batches: 1 Memory Usage: 721824kB
10. 2,318.539 82,679.820 ↑ 12.7 1,402,867 3 / 3

Parallel Hash Join (cost=370,284.12..3,796,642.78 rows=17,803,142 width=16) (actual time=17,961.032..82,679.820 rows=1,402,867 loops=3)

  • Hash Cond: (pitch_2.synergy_event_id = syn.event_id)
11. 62,411.163 62,411.163 ↑ 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.006..62,411.163 rows=14,242,513 loops=3)

12. 666.812 17,950.118 ↑ 1.3 1,516,579 3 / 3

Parallel Hash (cost=346,581.47..346,581.47 rows=1,896,212 width=25) (actual time=17,950.117..17,950.118 rows=1,516,579 loops=3)

  • Buckets: 8388608 Batches: 1 Memory Usage: 350528kB
13. 17,283.306 17,283.306 ↑ 1.3 1,516,579 3 / 3

Parallel Index Only Scan using event_pkey on event syn (cost=0.56..346,581.47 rows=1,896,212 width=25) (actual time=0.359..17,283.306 rows=1,516,579 loops=3)

  • Heap Fetches: 255054
14. 27.907 319.795 ↑ 1.2 103,551 3 / 3

Parallel Hash (cost=7,947.18..7,947.18 rows=129,398 width=4) (actual time=319.795..319.795 rows=103,551 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 16288kB
15. 291.888 291.888 ↑ 1.2 103,551 3 / 3

Parallel Index Only Scan using game_pkey on game game_1 (cost=0.42..7,947.18 rows=129,398 width=4) (actual time=0.455..291.888 rows=103,551 loops=3)

  • Heap Fetches: 40685
16. 498.865 22,144.632 ↑ 1.3 1,218,122 3 / 3

Parallel Hash (cost=111,837.71..111,837.71 rows=1,536,513 width=16) (actual time=22,144.632..22,144.632 rows=1,218,122 loops=3)

  • Buckets: 4194304 Batches: 1 Memory Usage: 204288kB
17. 21,645.767 21,645.767 ↑ 1.3 1,218,122 3 / 3

Parallel Index Only Scan using pitch_tracking_statcast_pkey on pitch_tracking_statcast sc (cost=0.43..111,837.71 rows=1,536,513 width=16) (actual time=0.686..21,645.767 rows=1,218,122 loops=3)

  • Heap Fetches: 163124
18. 0.001 0.013 ↓ 0.0 0 3 / 3

Hash (cost=11.30..11.30 rows=130 width=16) (actual time=0.013..0.013 rows=0 loops=3)

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

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

20. 2,547.275 68,235.195 ↑ 1.3 5,944,459 3 / 3

Parallel Hash (cost=499,617.02..499,617.02 rows=7,432,293 width=16) (actual time=68,235.195..68,235.195 rows=5,944,459 loops=3)

  • Buckets: 33554432 Batches: 1 Memory Usage: 1098944kB
21. 65,687.920 65,687.920 ↑ 1.3 5,944,459 3 / 3

Parallel Index Only Scan using pitch_tracking_trackman_pkey on pitch_tracking_trackman tm (cost=0.56..499,617.02 rows=7,432,293 width=16) (actual time=0.501..65,687.920 rows=5,944,459 loops=3)

  • Heap Fetches: 307005
22. 3.052 16.888 ↓ 40.0 40 3 / 3

Parallel Hash (cost=10,751.20..10,751.20 rows=1 width=23) (actual time=16.888..16.888 rows=40 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
23. 0.018 13.836 ↓ 40.0 40 3 / 3

Nested Loop (cost=122.62..10,751.20 rows=1 width=23) (actual time=8.109..13.836 rows=40 loops=3)

24. 0.668 13.461 ↓ 40.0 40 3 / 3

Hash Join (cost=122.06..10,747.76 rows=1 width=20) (actual time=7.970..13.461 rows=40 loops=3)

  • Hash Cond: (pitch.game_id = game.game_id)
25. 1.087 12.783 ↓ 11.0 6,314 3 / 3

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

26. 7.237 7.237 ↑ 1.0 1 3 / 3

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

  • Filter: (mlb_person_id = ANY ('{628317,621111}'::integer[]))
  • Rows Removed by Filter: 29210
27. 4.459 4.459 ↓ 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.016..6.689 rows=9,470 loops=2)

  • Index Cond: (pitcher_id = player.player_id)
28. 0.002 0.010 ↑ 30.5 2 2 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.008 0.008 ↑ 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.011..0.012 rows=2 loops=2)

  • Index Cond: (game_date = '2019-10-03'::date)
30. 0.357 0.357 ↑ 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.009..0.009 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 : 2.530 ms
Execution time : 238,018.215 ms