explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7rJX

Settings
# exclusive inclusive rows x rows loops node
1. 540.242 100,917.041 ↓ 1,028.3 234,446 1

Sort (cost=92,895.51..92,896.08 rows=228 width=732) (actual time=100,856.444..100,917.041 rows=234,446 loops=1)

  • Sort Key: final.game_date
  • Sort Method: external merge Disk: 59464kB
2.          

CTE source

3. 260.091 1,848.297 ↑ 1.0 234,446 1

Unique (cost=56,663.44..66,041.28 rows=234,446 width=236) (actual time=1,480.553..1,848.297 rows=234,446 loops=1)

4. 1,458.924 1,588.206 ↑ 1.0 234,446 1

Sort (cost=56,663.44..57,249.55 rows=234,446 width=236) (actual time=1,480.551..1,588.206 rows=234,446 loops=1)

  • Sort Key: covers_picks.game_date, covers_picks.username, covers_picks.season_start_year, covers_picks.league, covers_picks.home_team_name, covers_picks.away_team_name, covers_picks.home_team_score, covers_picks.away_team_score, covers_picks.pick_side, covers_picks.pick_spread, covers_picks.pick_result, covers_picks.units_result, covers_picks.units_wagered, covers_picks.pulled_at, (0)
  • Sort Method: external merge Disk: 24168kB
5. 27.710 129.282 ↑ 1.0 234,446 1

Append (cost=0.00..9,306.15 rows=234,446 width=236) (actual time=0.020..129.282 rows=234,446 loops=1)

6. 101.515 101.515 ↑ 1.0 234,380 1

Seq Scan on covers_picks (cost=0.00..5,787.80 rows=234,380 width=83) (actual time=0.019..101.515 rows=234,380 loops=1)

7. 0.017 0.057 ↑ 1.0 66 1

Subquery Scan on *SELECT* 2 (cost=0.00..2.32 rows=66 width=110) (actual time=0.020..0.057 rows=66 loops=1)

8. 0.040 0.040 ↑ 1.0 66 1

Seq Scan on pending_picks (cost=0.00..1.66 rows=66 width=86) (actual time=0.017..0.040 rows=66 loops=1)

9.          

CTE users

10. 1.217 7.316 ↑ 1.0 777 1

WindowAgg (cost=65.07..84.50 rows=777 width=175) (actual time=6.020..7.316 rows=777 loops=1)

11. 5.765 6.099 ↑ 1.0 777 1

Sort (cost=65.07..67.02 rows=777 width=167) (actual time=6.007..6.099 rows=777 loops=1)

  • Sort Key: users.username, users.season_start_year, users.league, users.pulled_at DESC
  • Sort Method: quicksort Memory: 231kB
12. 0.334 0.334 ↑ 1.0 777 1

Seq Scan on users (cost=0.00..27.77 rows=777 width=167) (actual time=0.013..0.334 rows=777 loops=1)

13.          

CTE seasons

14. 0.012 0.012 ↑ 58.3 6 1

Seq Scan on seasons (cost=0.00..13.50 rows=350 width=204) (actual time=0.011..0.012 rows=6 loops=1)

15.          

CTE doubleheaders

16. 0.015 0.015 ↑ 44.8 33 1

Seq Scan on doubleheaders (cost=0.00..24.80 rows=1,480 width=27) (actual time=0.012..0.015 rows=33 loops=1)

17.          

CTE casted

18. 550.568 84,484.489 ↓ 1,028.3 234,446 1

Hash Left Join (cost=20.40..26,483.13 rows=228 width=438) (actual time=1,486.859..84,484.489 rows=234,446 loops=1)

  • Hash Cond: (((source.away_team_name)::text = away_team.team_name) AND (seasons_1.league = away_team.league))
19. 262.522 83,933.840 ↓ 1,028.3 234,446 1

Hash Left Join (cost=16.32..26,468.22 rows=228 width=371) (actual time=1,486.755..83,933.840 rows=234,446 loops=1)

  • Hash Cond: (((source.home_team_name)::text = home_team.team_name) AND (seasons_1.league = home_team.league))
20. 21,904.300 83,671.233 ↓ 1,028.3 234,446 1

Nested Loop Left Join (cost=12.25..26,460.71 rows=228 width=368) (actual time=1,486.651..83,671.233 rows=234,446 loops=1)

  • Join Filter: (((source.username)::text = (users_1.username)::text) AND (source.league = users_1.league) AND (source.season_start_year = users_1.season_start_year))
  • Rows Removed by Join Filter: 180288974
21. 435.716 2,452.095 ↓ 1,028.3 234,446 1

Hash Join (cost=12.25..26,418.19 rows=228 width=364) (actual time=1,480.602..2,452.095 rows=234,446 loops=1)

  • Hash Cond: ((source.league = seasons_1.league) AND (source.season_start_year = seasons_1.season_start_year))
  • Join Filter: ((source.game_date >= seasons_1.start_date) AND (source.game_date <= seasons_1.end_date))
  • Rows Removed by Join Filter: 216319
22. 2,016.352 2,016.352 ↑ 1.0 234,446 1

CTE Scan on source (cost=0.00..4,688.92 rows=234,446 width=236) (actual time=1,480.555..2,016.352 rows=234,446 loops=1)

23. 0.007 0.027 ↑ 58.3 6 1

Hash (cost=7.00..7.00 rows=350 width=140) (actual time=0.027..0.027 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.020 0.020 ↑ 58.3 6 1

CTE Scan on seasons seasons_1 (cost=0.00..7.00 rows=350 width=140) (actual time=0.015..0.020 rows=6 loops=1)

25. 59,314.838 59,314.838 ↓ 192.5 770 234,446

CTE Scan on users users_1 (cost=0.00..17.48 rows=4 width=72) (actual time=0.000..0.253 rows=770 loops=234,446)

  • Filter: (user_season_start_year_row_number = 1)
  • Rows Removed by Filter: 7
26. 0.052 0.085 ↑ 1.0 123 1

Hash (cost=2.23..2.23 rows=123 width=16) (actual time=0.085..0.085 rows=123 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
27. 0.033 0.033 ↑ 1.0 123 1

Seq Scan on teams home_team (cost=0.00..2.23 rows=123 width=16) (actual time=0.008..0.033 rows=123 loops=1)

28. 0.051 0.081 ↑ 1.0 123 1

Hash (cost=2.23..2.23 rows=123 width=16) (actual time=0.081..0.081 rows=123 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
29. 0.030 0.030 ↑ 1.0 123 1

Seq Scan on teams away_team (cost=0.00..2.23 rows=123 width=16) (actual time=0.005..0.030 rows=123 loops=1)

30.          

CTE join_doubleheaders

31. 173.545 86,197.761 ↓ 1,028.3 234,446 1

Merge Left Join (cost=121.02..146.65 rows=228 width=500) (actual time=85,950.879..86,197.761 rows=234,446 loops=1)

  • Merge Cond: ((data.game_date = double.game_date) AND (data.home_team_abbreviation = double.home_team_abbreviation) AND (data.away_team_abbreviation = double.away_team_abbreviation) AND (data.home_team_score = double.home_team_score) AND (data.away_team_score = double.away_team_score))
32. 860.358 86,024.147 ↓ 1,028.3 234,446 1

Sort (cost=13.49..14.06 rows=228 width=496) (actual time=85,950.805..86,024.147 rows=234,446 loops=1)

  • Sort Key: data.game_date, data.home_team_abbreviation, data.away_team_abbreviation, data.home_team_score, data.away_team_score
  • Sort Method: external merge Disk: 35808kB
33. 85,163.789 85,163.789 ↓ 1,028.3 234,446 1

CTE Scan on casted data (cost=0.00..4.56 rows=228 width=496) (actual time=1,486.863..85,163.789 rows=234,446 loops=1)

34. 0.032 0.069 ↑ 44.8 33 1

Sort (cost=107.53..111.23 rows=1,480 width=76) (actual time=0.064..0.069 rows=33 loops=1)

  • Sort Key: double.game_date, double.home_team_abbreviation, double.away_team_abbreviation, double.home_team_score, double.away_team_score
  • Sort Method: quicksort Memory: 27kB
35. 0.037 0.037 ↑ 44.8 33 1

CTE Scan on doubleheaders double (cost=0.00..29.60 rows=1,480 width=76) (actual time=0.015..0.037 rows=33 loops=1)

36.          

CTE final

37. 849.257 98,966.793 ↓ 1,028.3 234,446 1

WindowAgg (cost=65.93..84.74 rows=228 width=700) (actual time=97,670.225..98,966.793 rows=234,446 loops=1)

38. 2,545.231 98,117.536 ↓ 1,028.3 234,446 1

Sort (cost=65.93..66.50 rows=228 width=628) (actual time=97,670.204..98,117.536 rows=234,446 loops=1)

  • Sort Key: join_doubleheaders.away_team_abbreviation, join_doubleheaders.username, join_doubleheaders.league, join_doubleheaders.game_date, join_doubleheaders.pulled_at DESC, join_doubleheaders.home_team_abbreviation
  • Sort Method: external merge Disk: 44832kB
39. 624.954 95,572.305 ↓ 1,028.3 234,446 1

WindowAgg (cost=50.16..57.00 rows=228 width=628) (actual time=94,586.377..95,572.305 rows=234,446 loops=1)

40. 2,181.814 94,947.351 ↓ 1,028.3 234,446 1

Sort (cost=50.16..50.73 rows=228 width=596) (actual time=94,586.366..94,947.351 rows=234,446 loops=1)

  • Sort Key: join_doubleheaders.home_team_abbreviation, join_doubleheaders.username, join_doubleheaders.league, join_doubleheaders.game_date, join_doubleheaders.pulled_at DESC
  • Sort Method: external merge Disk: 42744kB
41. 667.168 92,765.537 ↓ 1,028.3 234,446 1

WindowAgg (cost=34.39..41.23 rows=228 width=596) (actual time=91,766.270..92,765.537 rows=234,446 loops=1)

42. 1,816.521 92,098.369 ↓ 1,028.3 234,446 1

Sort (cost=34.39..34.96 rows=228 width=564) (actual time=91,766.259..92,098.369 rows=234,446 loops=1)

  • Sort Key: join_doubleheaders.username, join_doubleheaders.pick_side, join_doubleheaders.league, join_doubleheaders.game_date, join_doubleheaders.pulled_at DESC, join_doubleheaders.home_team_abbreviation
  • Sort Method: external merge Disk: 40472kB
43. 926.208 90,281.848 ↓ 1,028.3 234,446 1

WindowAgg (cost=13.49..25.46 rows=228 width=564) (actual time=88,606.101..90,281.848 rows=234,446 loops=1)

44. 595.248 89,355.640 ↓ 1,028.3 234,446 1

WindowAgg (cost=13.49..19.76 rows=228 width=532) (actual time=88,606.094..89,355.640 rows=234,446 loops=1)

45. 1,999.937 88,760.392 ↓ 1,028.3 234,446 1

Sort (cost=13.49..14.06 rows=228 width=500) (actual time=88,606.079..88,760.392 rows=234,446 loops=1)

  • Sort Key: join_doubleheaders.username, join_doubleheaders.league, join_doubleheaders.game_date, join_doubleheaders.pulled_at DESC, join_doubleheaders.home_team_abbreviation
  • Sort Method: external merge Disk: 36472kB
46. 86,760.455 86,760.455 ↓ 1,028.3 234,446 1

CTE Scan on join_doubleheaders (cost=0.00..4.56 rows=228 width=500) (actual time=85,950.885..86,760.455 rows=234,446 loops=1)

47. 100,376.799 100,376.799 ↓ 1,028.3 234,446 1

CTE Scan on final (cost=0.00..7.98 rows=228 width=732) (actual time=97,670.264..100,376.799 rows=234,446 loops=1)

Planning time : 2.945 ms
Execution time : 100,979.015 ms