explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PX1Y

Settings
# exclusive inclusive rows x rows loops node
1. 73.622 211,523.874 ↓ 4.3 51,840 1

Sort (cost=17,480.47..17,510.47 rows=12,000 width=381) (actual time=211,514.425..211,523.874 rows=51,840 loops=1)

  • Sort Key: matchups.pitcher, matchups.batter
  • Sort Method: quicksort Memory: 15307kB
2.          

CTE next_series

3. 0.001 18.556 ↑ 1.0 1 1

Limit (cost=1,535.16..1,535.16 rows=1 width=37) (actual time=18.556..18.556 rows=1 loops=1)

4. 0.086 18.555 ↑ 13.0 1 1

Sort (cost=1,535.16..1,535.19 rows=13 width=37) (actual time=18.555..18.555 rows=1 loops=1)

  • Sort Key: mlbam_daily_schedule.game_time_et
  • Sort Method: top-N heapsort Memory: 25kB
5. 0.192 18.469 ↓ 3.7 48 1

Nested Loop (cost=0.41..1,535.10 rows=13 width=37) (actual time=4.901..18.469 rows=48 loops=1)

6. 17.893 17.893 ↑ 1.3 48 1

Seq Scan on mlbam_daily_schedule (cost=0.00..1,252.55 rows=64 width=44) (actual time=4.860..17.893 rows=48 loops=1)

  • Filter: ((ser_game_nbr = 1) AND ((away_team_id = 119) OR (home_team_id = 119)) AND (game_time_et >= timezone('EDT'::text, now())))
  • Rows Removed by Filter: 15014
7. 0.384 0.384 ↑ 1.0 1 48

Index Scan using pk_staging_mlbam_daily_team_history on mlbam_daily_team_history opp (cost=0.41..4.40 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=48)

  • Index Cond: ((team_id = CASE WHEN (mlbam_daily_schedule.away_team_id = 119) THEN mlbam_daily_schedule.home_team_id ELSE mlbam_daily_schedule.away_team_id END) AND (year = mlbam_daily_schedule.year))
8.          

CTE pitchers

9. 0.067 32.120 ↓ 8.0 24 1

HashAggregate (cost=7,549.22..7,549.25 rows=3 width=36) (actual time=32.089..32.120 rows=24 loops=1)

  • Group Key: (((lad.name_last || ', '::text) || lad.name_use)), lad.mlbam_id
10. 0.033 32.053 ↓ 8.0 24 1

Append (cost=0.00..7,549.21 rows=3 width=36) (actual time=8.711..32.053 rows=24 loops=1)

11. 32.017 32.017 ↓ 22.0 22 1

Seq Scan on vwm_player_bio lad (cost=0.00..7,549.15 rows=1 width=36) (actual time=8.710..32.017 rows=22 loops=1)

  • Filter: ((org = 'LA'::text) AND (forty_man = 1) AND (primary_position = 1))
  • Rows Removed by Filter: 119553
12. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

14.          

CTE batters

15. 0.204 47.629 ↓ 4.5 18 1

Nested Loop (cost=0.00..7,222.17 rows=4 width=36) (actual time=10.360..47.629 rows=18 loops=1)

  • Join Filter: (next_series_1.opponent = opp_1.org)
  • Rows Removed by Join Filter: 508
16. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on next_series next_series_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.003 rows=1 loops=1)

17. 47.422 47.422 ↑ 2.0 526 1

Seq Scan on vwm_player_bio opp_1 (cost=0.00..7,208.70 rows=1,074 width=20) (actual time=10.259..47.422 rows=526 loops=1)

  • Filter: ((primary_position <> 1) AND (forty_man = 1))
  • Rows Removed by Filter: 119049
18.          

CTE matchups

19. 0.487 80.769 ↓ 36.0 432 1

Nested Loop (cost=0.00..0.34 rows=12 width=72) (actual time=42.458..80.769 rows=432 loops=1)

20. 32.162 32.162 ↓ 8.0 24 1

CTE Scan on pitchers (cost=0.00..0.06 rows=3 width=36) (actual time=32.092..32.162 rows=24 loops=1)

21. 48.120 48.120 ↓ 4.5 18 24

CTE Scan on batters (cost=0.00..0.08 rows=4 width=36) (actual time=0.432..2.005 rows=18 loops=24)

22. 18.753 211,450.252 ↓ 4.3 51,840 1

Nested Loop (cost=0.25..360.51 rows=12,000 width=381) (actual time=563.066..211,450.252 rows=51,840 loops=1)

23. 18.559 18.559 ↑ 1.0 1 1

CTE Scan on next_series (cost=0.00..0.02 rows=1 width=38) (actual time=18.558..18.559 rows=1 loops=1)

24. 30.348 211,412.940 ↓ 4.3 51,840 1

Nested Loop (cost=0.25..240.49 rows=12,000 width=381) (actual time=544.502..211,412.940 rows=51,840 loops=1)

25. 81.456 81.456 ↓ 36.0 432 1

CTE Scan on matchups (cost=0.00..0.24 rows=12 width=72) (actual time=42.459..81.456 rows=432 loops=1)

26. 211,301.136 211,301.136 ↑ 8.3 120 432

Function Scan on advance_scouting_bullpen_sheet bps (cost=0.25..10.25 rows=1,000 width=317) (actual time=489.102..489.123 rows=120 loops=432)

Planning time : 0.831 ms
Execution time : 211,532.184 ms