explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uxwb

Settings
# exclusive inclusive rows x rows loops node
1. 8.843 1,001.561 ↓ 15,080.0 15,080 1

Merge Join (cost=670,297.79..670,477.60 rows=1 width=145) (actual time=989.987..1,001.561 rows=15,080 loops=1)

  • Merge Cond: ((tm.game_date = mlb.game_date) AND (tm.batter_id = mlb.batter_id) AND (tm.pitcher_id = mlb.pitcher_id))
2.          

CTE tm

3. 912.542 912.542 ↓ 104.1 22,387 1

Index Scan using trackman_game_id_pitch_no_idx on trackman (cost=0.56..623,676.66 rows=215 width=51) (actual time=589.610..912.542 rows=22,387 loops=1)

  • Index Cond: (pitch_no = 1)
  • Filter: (date_part('year'::text, (date)::timestamp without time zone) = '2019'::double precision)
  • Rows Removed by Filter: 72,194
4.          

CTE mlb

5. 0.817 44.138 ↓ 1.0 18,305 1

Gather (cost=1,000.56..44,999.38 rows=17,764 width=16) (actual time=0.604..44.138 rows=18,305 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 7.505 43.321 ↑ 1.2 6,102 3 / 3

Nested Loop (cost=0.56..42,222.98 rows=7,402 width=16) (actual time=0.072..43.321 rows=6,102 loops=3)

7. 17.192 17.192 ↑ 1.3 6,208 3 / 3

Parallel Seq Scan on schedule (cost=0.00..23,057.25 rows=7,869 width=8) (actual time=0.048..17.192 rows=6,208 loops=3)

  • Filter: (season = '2019'::text)
  • Rows Removed by Filter: 86,096
8. 18.624 18.624 ↑ 8.0 1 18,624 / 3

Index Scan using play_pkey on play (cost=0.56..15.55 rows=8 width=12) (actual time=0.003..0.003 rows=1 loops=18,624)

  • Index Cond: ((game_pk = schedule.game_pk) AND (ab_index = 0))
9. 15.906 937.186 ↓ 104.1 22,387 1

Sort (cost=12.63..13.17 rows=215 width=108) (actual time=935.398..937.186 rows=22,387 loops=1)

  • Sort Key: tm.game_date, tm.batter_id, tm.pitcher_id
  • Sort Method: quicksort Memory: 3,481kB
10. 921.280 921.280 ↓ 104.1 22,387 1

CTE Scan on tm (cost=0.00..4.30 rows=215 width=108) (actual time=589.613..921.280 rows=22,387 loops=1)

11. 7.947 55.532 ↑ 1.0 17,439 1

Sort (cost=1,609.12..1,653.53 rows=17,764 width=16) (actual time=54.564..55.532 rows=17,439 loops=1)

  • Sort Key: mlb.game_date, mlb.batter_id, mlb.pitcher_id
  • Sort Method: quicksort Memory: 1,627kB
12. 47.585 47.585 ↓ 1.0 18,305 1

CTE Scan on mlb (cost=0.00..355.28 rows=17,764 width=16) (actual time=0.605..47.585 rows=18,305 loops=1)

Planning time : 0.365 ms
Execution time : 1,003.338 ms