explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TdvL : pitcher game log

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 1,657.446 ↓ 4.0 4 1

Sort (cost=106,072.11..106,072.11 rows=1 width=141) (actual time=1,657.445..1,657.446 rows=4 loops=1)

  • Sort Key: gc.game_date, gc.game_num
  • Sort Method: quicksort Memory: 26kB
2. 0.053 1,657.427 ↓ 4.0 4 1

Nested Loop (cost=150.95..106,072.10 rows=1 width=141) (actual time=1,004.746..1,657.427 rows=4 loops=1)

  • Join Filter: (t.team_id = pg.team_id)
3. 0.055 1,654.834 ↓ 4.0 4 1

Nested Loop (cost=150.52..105,947.95 rows=1 width=170) (actual time=1,003.964..1,654.834 rows=4 loops=1)

  • Join Filter: ((t.team_id = toh.team_id) AND (os.organization_id = toh.organization_id) AND (os.season >= ((toh.start_season)::character varying)::ltree) AND (os.season <= ((toh.end_season)::character varying)::ltree))
  • Rows Removed by Join Filter: 32
4. 0.076 1,654.671 ↓ 36.0 36 1

Nested Loop (cost=150.24..105,947.48 rows=1 width=174) (actual time=1,003.896..1,654.671 rows=36 loops=1)

  • Join Filter: ((os.season >= th.start_season) AND (os.season <= th.end_season))
  • Rows Removed by Join Filter: 108
5. 0.044 1,654.451 ↓ 36.0 36 1

Nested Loop (cost=149.96..105,946.94 rows=1 width=170) (actual time=1,003.878..1,654.451 rows=36 loops=1)

6. 83.780 1,654.335 ↓ 36.0 36 1

Nested Loop (cost=149.68..105,946.51 rows=1 width=166) (actual time=1,003.861..1,654.335 rows=36 loops=1)

7. 112.749 1,414.018 ↓ 156,537.0 156,537 1

Nested Loop (cost=149.25..105,944.75 rows=1 width=94) (actual time=964.792..1,414.018 rows=156,537 loops=1)

8. 22.446 1,142.221 ↓ 159,048.0 159,048 1

Nested Loop (cost=148.83..105,940.90 rows=1 width=82) (actual time=964.772..1,142.221 rows=159,048 loops=1)

9. 81.496 1,084.431 ↓ 2.0 17,672 1

Subquery Scan on gc (cost=1.66..105,636.13 rows=8,953 width=37) (actual time=961.986..1,084.431 rows=17,672 loops=1)

  • Filter: (gc.official_status AND (gc.season = '2019'::ltree) AND (gc.game_type_code = 'R'::bpchar))
  • Rows Removed by Filter: 448,714
10. 484.328 1,002.935 ↓ 1.0 466,386 1

WindowAgg (cost=1.66..98,640.36 rows=466,385 width=541) (actual time=0.052..1,002.935 rows=466,386 loops=1)

11. 218.179 518.607 ↓ 1.0 466,386 1

Merge Left Join (cost=1.66..89,312.66 rows=466,385 width=37) (actual time=0.034..518.607 rows=466,386 loops=1)

  • Merge Cond: (g.game_id = gd_1.game_id)
12. 126.573 126.573 ↓ 1.0 454,725 1

Index Scan using game_pkey on game g (cost=0.42..35,746.56 rows=454,724 width=21) (actual time=0.015..126.573 rows=454,725 loops=1)

13. 173.855 173.855 ↓ 1.0 466,386 1

Index Scan using idx_game_id on game_date gd_1 (cost=0.42..46,600.61 rows=466,385 width=24) (actual time=0.014..173.855 rows=466,386 loops=1)

14. 17.644 35.344 ↓ 9.0 9 17,672

Materialize (cost=147.16..192.85 rows=1 width=45) (actual time=0.000..0.002 rows=9 loops=17,672)

15. 0.702 17.700 ↓ 9.0 9 1

Hash Join (cost=147.16..192.85 rows=1 width=45) (actual time=2.780..17.700 rows=9 loops=1)

  • Hash Cond: (oh.organization_id = os.organization_id)
  • Join Filter: ((os.season >= oh.start_season) AND (os.season <= oh.end_season) AND ((SubPlan 3) = 1))
  • Rows Removed by Join Filter: 1,155
16. 0.177 0.177 ↑ 1.0 1,480 1

Seq Scan on organization_history oh (cost=0.00..41.80 rows=1,480 width=54) (actual time=0.010..0.177 rows=1,480 loops=1)

17. 0.028 1.113 ↑ 1.0 119 1

Hash (cost=145.68..145.68 rows=119 width=17) (actual time=1.112..1.113 rows=119 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
18. 1.085 1.085 ↑ 1.0 119 1

Seq Scan on organization_season os (cost=0.00..145.68 rows=119 width=17) (actual time=0.967..1.085 rows=119 loops=1)

  • Filter: ((season IS NOT NULL) AND (season = '2019'::ltree))
  • Rows Removed by Filter: 5,775
19.          

SubPlan (for Hash Join)

20. 0.119 15.708 ↑ 1.0 1 119

Limit (cost=0.27..61.61 rows=1 width=4) (actual time=0.131..0.132 rows=1 loops=119)

21. 0.280 15.589 ↑ 1.0 1 119

Nested Loop (cost=0.27..61.61 rows=1 width=4) (actual time=0.131..0.131 rows=1 loops=119)

22. 14.875 14.875 ↓ 2.0 2 119

Seq Scan on organization_history dh_2 (cost=0.00..52.90 rows=1 width=4) (actual time=0.054..0.125 rows=2 loops=119)

  • Filter: ((organization_path @> oh.organization_path) AND (os.season >= start_season) AND (os.season <= end_season))
  • Rows Removed by Filter: 582
23. 0.434 0.434 ↑ 1.0 1 217

Index Scan using organization_pkey on organization d_2 (cost=0.27..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=217)

  • Index Cond: (organization_id = dh_2.organization_id)
  • Filter: ((organization_id <> 5150) AND ((organization_type_code)::bpchar = 'S'::bpchar))
  • Rows Removed by Filter: 0
24. 159.048 159.048 ↑ 1.0 1 159,048

Index Scan using game_detail_pkey on game_detail gd (cost=0.42..3.85 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=159,048)

  • Index Cond: (game_id = gc.game_id)
25. 156.537 156.537 ↓ 0.0 0 156,537

Index Scan using batting_player_game_pkey on batting_game bg (cost=0.43..1.75 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=156,537)

  • Index Cond: ((game_id = gd.game_id) AND (person_id = 571,704))
26. 0.072 0.072 ↑ 1.0 1 36

Index Only Scan using team_pkey on team t (cost=0.28..0.43 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=36)

  • Index Cond: (team_id = bg.team_id)
  • Heap Fetches: 36
27. 0.144 0.144 ↓ 2.0 4 36

Index Scan using team_history_pkey on team_history th (cost=0.28..0.51 rows=2 width=30) (actual time=0.002..0.004 rows=4 loops=36)

  • Index Cond: (team_id = t.team_id)
28. 0.108 0.108 ↑ 2.0 1 36

Index Scan using team_organization_history_pkey on team_organization_history toh (cost=0.28..0.39 rows=2 width=34) (actual time=0.003..0.003 rows=1 loops=36)

  • Index Cond: (team_id = th.team_id)
  • Filter: ((organization_id <> ALL ('{114,115}'::integer[])) OR (organization_id IS NULL))
  • Rows Removed by Filter: 1
29. 0.044 0.044 ↑ 1.0 1 4

Index Scan using playing_player_game_pkey on playing_game pg (cost=0.43..1.20 rows=1 width=39) (actual time=0.011..0.011 rows=1 loops=4)

  • Index Cond: ((game_id = gd.game_id) AND (person_id = 571,704))
30.          

SubPlan (for Nested Loop)

31. 0.012 1.388 ↑ 1.0 1 4

Limit (cost=0.27..61.61 rows=1 width=4) (actual time=0.346..0.347 rows=1 loops=4)

32. 0.028 1.376 ↑ 1.0 1 4

Nested Loop (cost=0.27..61.61 rows=1 width=4) (actual time=0.344..0.344 rows=1 loops=4)

33. 1.312 1.312 ↓ 3.0 3 4

Seq Scan on organization_history dh (cost=0.00..52.90 rows=1 width=4) (actual time=0.117..0.328 rows=3 loops=4)

  • Filter: ((organization_path @> oh.organization_path) AND (os.season >= start_season) AND (os.season <= end_season))
  • Rows Removed by Filter: 1,475
34. 0.036 0.036 ↓ 0.0 0 12

Index Scan using organization_pkey on organization d (cost=0.27..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=12)

  • Index Cond: (organization_id = dh.organization_id)
  • Filter: ((organization_id <> 5150) AND ((organization_type_code)::bpchar = 'S'::bpchar))
  • Rows Removed by Filter: 1
35. 0.016 1.108 ↑ 1.0 1 4

Nested Loop (cost=0.27..61.32 rows=1 width=4) (actual time=0.132..0.277 rows=1 loops=4)

36. 1.068 1.068 ↓ 3.0 3 4

Seq Scan on organization_history dh_1 (cost=0.00..52.90 rows=1 width=4) (actual time=0.125..0.267 rows=3 loops=4)

  • Filter: ((organization_path @> oh.organization_path) AND (os.season >= start_season) AND (os.season <= end_season))
  • Rows Removed by Filter: 1,477
37. 0.024 0.024 ↓ 0.0 0 12

Index Scan using organization_pkey on organization d_1 (cost=0.27..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=12)

  • Index Cond: (organization_id = dh_1.organization_id)
  • Filter: ((organization_type_code)::bpchar = 'L'::bpchar)
  • Rows Removed by Filter: 1
Planning time : 32.766 ms
Execution time : 1,657.704 ms