explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 82p3

Settings
# exclusive inclusive rows x rows loops node
1. 1.082 2,173.744 ↓ 257.0 514 1

Sort (cost=7,158.18..7,158.19 rows=2 width=152) (actual time=2,173.648..2,173.744 rows=514 loops=1)

  • Sort Key: t.age_group, t.skill_level, (sum(sub.points_awarded)) DESC, (CASE WHEN (((gc.num_games >= 6)) AND t.is_committed_to_playoffs) THEN dense_rank() OVER (?) ELSE NULL::bigint END)
  • Sort Method: quicksort Memory: 97kB
2.          

CTE game_counts

3. 1.523 86.286 ↓ 257.0 514 1

GroupAggregate (cost=1,221.45..1,221.49 rows=2 width=12) (actual time=83.508..86.286 rows=514 loops=1)

  • Group Key: games.home_team
4. 4.013 84.763 ↓ 2,707.0 5,414 1

Sort (cost=1,221.45..1,221.46 rows=2 width=4) (actual time=83.494..84.763 rows=5,414 loops=1)

  • Sort Key: games.home_team
  • Sort Method: quicksort Memory: 446kB
5. 2.236 80.750 ↓ 2,707.0 5,414 1

Append (cost=556.31..1,221.44 rows=2 width=4) (actual time=31.579..80.750 rows=5,414 loops=1)

6. 2.966 37.988 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.31..610.71 rows=1 width=4) (actual time=31.578..37.988 rows=2,707 loops=1)

7. 0.760 32.315 ↓ 2,707.0 2,707 1

Hash Right Join (cost=556.03..594.09 rows=1 width=24) (actual time=31.569..32.315 rows=2,707 loops=1)

  • Hash Cond: (fg.game_id = g.id)
8.          

CTE admin_entries

9. 0.031 0.031 ↓ 1.1 76 1

Seq Scan on admin_reported_scores (cost=0.00..1.69 rows=69 width=13) (actual time=0.010..0.031 rows=76 loops=1)

10.          

CTE self_reported_entries

11. 1.393 11.500 ↓ 134.5 2,690 1

Subquery Scan on sub_1 (cost=404.69..531.79 rows=20 width=13) (actual time=5.921..11.500 rows=2,690 loops=1)

  • Filter: (sub_1.rank = 1)
  • Rows Removed by Filter: 1,177
12. 3.335 10.107 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=5.919..10.107 rows=3,867 loops=1)

13. 2.737 6.772 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=5.911..6.772 rows=3,867 loops=1)

  • Sort Key: reported_scores.game_id, reported_scores.time_reported
  • Sort Method: quicksort Memory: 399kB
14. 2.814 4.035 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.822..4.035 rows=3,867 loops=1)

  • Group Key: reported_scores.game_id, reported_scores.home_team_runs, reported_scores.away_team_runs, reported_scores.time_reported
15. 1.221 1.221 ↑ 1.0 3,867 1

Seq Scan on reported_scores (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.006..1.221 rows=3,867 loops=1)

16.          

CTE score_results

17. 2.678 23.251 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=19.930..23.251 rows=2,766 loops=1)

18. 1.920 20.573 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=19.924..20.573 rows=2,766 loops=1)

  • Sort Key: admin_entries.game_id, admin_entries.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
19. 2.118 18.653 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=17.775..18.653 rows=2,766 loops=1)

  • Group Key: admin_entries.game_id, admin_entries.home_team_runs, admin_entries.away_team_runs, admin_entries.was_admin_reported
20. 2.316 16.535 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=15.680..16.535 rows=2,766 loops=1)

  • Group Key: admin_entries.game_id, admin_entries.home_team_runs, admin_entries.away_team_runs, admin_entries.was_admin_reported
21. 1.094 14.219 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.013..14.219 rows=2,766 loops=1)

22. 0.075 0.075 ↓ 1.1 76 1

CTE Scan on admin_entries (cost=0.00..1.38 rows=69 width=13) (actual time=0.012..0.075 rows=76 loops=1)

23. 13.050 13.050 ↓ 134.5 2,690 1

CTE Scan on self_reported_entries (cost=0.00..0.40 rows=20 width=13) (actual time=5.922..13.050 rows=2,690 loops=1)

24. 0.008 0.008 ↑ 2,040.0 1 1

Seq Scan on forfeited_games fg (cost=0.00..30.40 rows=2,040 width=4) (actual time=0.007..0.008 rows=1 loops=1)

25. 0.911 31.547 ↓ 2,707.0 2,707 1

Hash (cost=10.31..10.31 rows=1 width=8) (actual time=31.547..31.547 rows=2,707 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 138kB
26. 2.946 30.636 ↓ 2,707.0 2,707 1

Nested Loop (cost=0.28..10.31 rows=1 width=8) (actual time=19.955..30.636 rows=2,707 loops=1)

27. 24.977 24.977 ↓ 2,713.0 2,713 1

CTE Scan on score_results (cost=0.00..2.00 rows=1 width=4) (actual time=19.933..24.977 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
28. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = score_results.game_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 0
29. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using games_pk on games (cost=0.28..16.60 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = score_results.game_id)
  • Filter: ((NOT is_cancelled) AND ((game_date < '2020-10-12'::date) OR (SubPlan 1)))
30.          

SubPlan (for Index Scan)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using teams_pk on teams (cost=0.28..8.30 rows=1 width=1) (never executed)

  • Index Cond: (id = games.home_team)
32. 5.053 40.526 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.31..610.71 rows=1 width=4) (actual time=31.992..40.526 rows=2,707 loops=1)

33. 0.794 32.766 ↓ 2,707.0 2,707 1

Hash Right Join (cost=556.03..594.09 rows=1 width=24) (actual time=31.983..32.766 rows=2,707 loops=1)

  • Hash Cond: (fg_1.game_id = g_1.id)
34.          

CTE admin_entries

35. 0.027 0.027 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_1 (cost=0.00..1.69 rows=69 width=13) (actual time=0.007..0.027 rows=76 loops=1)

36.          

CTE self_reported_entries

37. 1.423 11.778 ↓ 134.5 2,690 1

Subquery Scan on sub_2 (cost=404.69..531.79 rows=20 width=13) (actual time=6.075..11.778 rows=2,690 loops=1)

  • Filter: (sub_2.rank = 1)
  • Rows Removed by Filter: 1,177
38. 3.389 10.355 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.074..10.355 rows=3,867 loops=1)

39. 2.761 6.966 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.066..6.966 rows=3,867 loops=1)

  • Sort Key: reported_scores_1.game_id, reported_scores_1.time_reported
  • Sort Method: quicksort Memory: 399kB
40. 3.012 4.205 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=3.003..4.205 rows=3,867 loops=1)

  • Group Key: reported_scores_1.game_id, reported_scores_1.home_team_runs, reported_scores_1.away_team_runs, reported_scores_1.time_reported
41. 1.193 1.193 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_1 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.005..1.193 rows=3,867 loops=1)

42.          

CTE score_results

43. 2.699 23.566 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=20.232..23.566 rows=2,766 loops=1)

44. 1.889 20.867 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=20.225..20.867 rows=2,766 loops=1)

  • Sort Key: admin_entries_1.game_id, admin_entries_1.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
45. 2.054 18.978 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=18.138..18.978 rows=2,766 loops=1)

  • Group Key: admin_entries_1.game_id, admin_entries_1.home_team_runs, admin_entries_1.away_team_runs, admin_entries_1.was_admin_reported
46. 2.369 16.924 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=16.050..16.924 rows=2,766 loops=1)

  • Group Key: admin_entries_1.game_id, admin_entries_1.home_team_runs, admin_entries_1.away_team_runs, admin_entries_1.was_admin_reported
47. 1.128 14.555 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.009..14.555 rows=2,766 loops=1)

48. 0.070 0.070 ↓ 1.1 76 1

CTE Scan on admin_entries admin_entries_1 (cost=0.00..1.38 rows=69 width=13) (actual time=0.008..0.070 rows=76 loops=1)

49. 13.357 13.357 ↓ 134.5 2,690 1

CTE Scan on self_reported_entries self_reported_entries_1 (cost=0.00..0.40 rows=20 width=13) (actual time=6.077..13.357 rows=2,690 loops=1)

50. 0.006 0.006 ↑ 2,040.0 1 1

Seq Scan on forfeited_games fg_1 (cost=0.00..30.40 rows=2,040 width=4) (actual time=0.005..0.006 rows=1 loops=1)

51. 0.888 31.966 ↓ 2,707.0 2,707 1

Hash (cost=10.31..10.31 rows=1 width=8) (actual time=31.966..31.966 rows=2,707 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 138kB
52. 3.038 31.078 ↓ 2,707.0 2,707 1

Nested Loop (cost=0.28..10.31 rows=1 width=8) (actual time=20.256..31.078 rows=2,707 loops=1)

53. 25.327 25.327 ↓ 2,713.0 2,713 1

CTE Scan on score_results score_results_1 (cost=0.00..2.00 rows=1 width=4) (actual time=20.235..25.327 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
54. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_1 (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = score_results_1.game_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 0
55. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using games_pk on games games_1 (cost=0.28..16.60 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = score_results_1.game_id)
  • Filter: ((NOT is_cancelled) AND ((game_date < '2020-10-12'::date) OR (SubPlan 5)))
56.          

SubPlan (for Index Scan)

57. 0.000 0.000 ↓ 0.0 0

Index Scan using teams_pk on teams teams_1 (cost=0.28..8.30 rows=1 width=1) (never executed)

  • Index Cond: (id = games_1.home_team)
58. 0.640 2,172.662 ↓ 257.0 514 1

WindowAgg (cost=5,936.60..5,936.68 rows=2 width=152) (actual time=2,171.932..2,172.662 rows=514 loops=1)

59. 1.535 2,172.022 ↓ 257.0 514 1

Sort (cost=5,936.60..5,936.60 rows=2 width=144) (actual time=2,171.913..2,172.022 rows=514 loops=1)

  • Sort Key: t.age_group, t.skill_level, ((gc.num_games >= 6)), t.is_committed_to_playoffs, (sum(sub.points_awarded)) DESC, ((sum(pf.wins) / (sum(pf.wins) + sum(pf.losses)))) DESC, (round((((sum(nr.runs_against))::double precision / (gc_1.num_games)::double precision))::numeric, 1)), (round((sum(tp.points) / sum(mp.max_points)), 3)), t.id
  • Sort Method: quicksort Memory: 97kB
60. 0.464 2,170.487 ↓ 257.0 514 1

Merge Left Join (cost=5,936.15..5,936.59 rows=2 width=144) (actual time=2,143.104..2,170.487 rows=514 loops=1)

  • Merge Cond: (t.id = tp.team_id)
61. 0.514 2,009.515 ↓ 257.0 514 1

Merge Left Join (cost=5,332.69..5,332.98 rows=2 width=111) (actual time=1,984.021..2,009.515 rows=514 loops=1)

  • Merge Cond: (t.id = nr.team_id)
62. 0.301 1,850.442 ↓ 257.0 514 1

Merge Left Join (cost=3,521.81..3,522.00 rows=2 width=79) (actual time=1,829.475..1,850.442 rows=514 loops=1)

  • Merge Cond: (t.id = pf.team_id)
63. 0.514 203.956 ↓ 257.0 514 1

Merge Left Join (cost=631.13..631.24 rows=2 width=47) (actual time=183.383..203.956 rows=514 loops=1)

  • Merge Cond: (t.id = sub.team_id)
64. 0.351 88.122 ↓ 257.0 514 1

Sort (cost=16.67..16.67 rows=2 width=39) (actual time=88.003..88.122 rows=514 loops=1)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 66kB
65. 0.697 87.771 ↓ 257.0 514 1

Nested Loop (cost=0.28..16.66 rows=2 width=39) (actual time=83.522..87.771 rows=514 loops=1)

66. 86.560 86.560 ↓ 257.0 514 1

CTE Scan on game_counts gc (cost=0.00..0.04 rows=2 width=12) (actual time=83.510..86.560 rows=514 loops=1)

67. 0.514 0.514 ↑ 1.0 1 514

Index Scan using teams_pk on teams t (cost=0.28..8.29 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=514)

  • Index Cond: (id = gc.team_id)
  • Filter: (NOT is_deleted)
68. 1.478 115.320 ↓ 514.0 514 1

GroupAggregate (cost=614.46..614.54 rows=1 width=12) (actual time=95.374..115.320 rows=514 loops=1)

  • Group Key: sub.team_id
69.          

CTE game_info

70. 3.229 51.356 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.86..611.38 rows=1 width=48) (actual time=31.869..51.356 rows=2,707 loops=1)

71. 3.200 45.420 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.58..611.04 rows=1 width=44) (actual time=31.860..45.420 rows=2,707 loops=1)

72. 1.322 39.513 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.31..610.71 rows=1 width=36) (actual time=31.849..39.513 rows=2,707 loops=1)

73. 0.948 32.777 ↓ 2,707.0 2,707 1

Hash Right Join (cost=556.03..594.09 rows=1 width=24) (actual time=31.839..32.777 rows=2,707 loops=1)

  • Hash Cond: (fg_2.game_id = g_2.id)
74.          

CTE admin_entries

75. 0.028 0.028 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_2 (cost=0.00..1.69 rows=69 width=13) (actual time=0.008..0.028 rows=76 loops=1)

76.          

CTE self_reported_entries

77. 1.406 11.683 ↓ 134.5 2,690 1

Subquery Scan on sub_3 (cost=404.69..531.79 rows=20 width=13) (actual time=6.044..11.683 rows=2,690 loops=1)

  • Filter: (sub_3.rank = 1)
  • Rows Removed by Filter: 1,177
78. 3.313 10.277 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.042..10.277 rows=3,867 loops=1)

79. 2.795 6.964 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.033..6.964 rows=3,867 loops=1)

  • Sort Key: reported_scores_2.game_id, reported_scores_2.time_reported
  • Sort Method: quicksort Memory: 399kB
80. 2.968 4.169 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.962..4.169 rows=3,867 loops=1)

  • Group Key: reported_scores_2.game_id, reported_scores_2.home_team_runs, reported_scores_2.away_team_runs, reported_scores_2.time_reported
81. 1.201 1.201 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_2 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.006..1.201 rows=3,867 loops=1)

82.          

CTE score_results

83. 2.725 23.350 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=19.982..23.350 rows=2,766 loops=1)

84. 1.880 20.625 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=19.973..20.625 rows=2,766 loops=1)

  • Sort Key: admin_entries_2.game_id, admin_entries_2.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
85. 2.051 18.745 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=17.908..18.745 rows=2,766 loops=1)

  • Group Key: admin_entries_2.game_id, admin_entries_2.home_team_runs, admin_entries_2.away_team_runs, admin_entries_2.was_admin_reported
86. 2.301 16.694 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=15.838..16.694 rows=2,766 loops=1)

  • Group Key: admin_entries_2.game_id, admin_entries_2.home_team_runs, admin_entries_2.away_team_runs, admin_entries_2.was_admin_reported
87. 1.096 14.393 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.011..14.393 rows=2,766 loops=1)

88. 0.072 0.072 ↓ 1.1 76 1

CTE Scan on admin_entries admin_entries_2 (cost=0.00..1.38 rows=69 width=13) (actual time=0.010..0.072 rows=76 loops=1)

89. 13.225 13.225 ↓ 134.5 2,690 1

CTE Scan on self_reported_entries self_reported_entries_2 (cost=0.00..0.40 rows=20 width=13) (actual time=6.046..13.225 rows=2,690 loops=1)

90. 0.008 0.008 ↑ 2,040.0 1 1

Seq Scan on forfeited_games fg_2 (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.007..0.008 rows=1 loops=1)

91. 0.970 31.821 ↓ 2,707.0 2,707 1

Hash (cost=10.31..10.31 rows=1 width=16) (actual time=31.821..31.821 rows=2,707 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 159kB
92. 3.029 30.851 ↓ 2,707.0 2,707 1

Nested Loop (cost=0.28..10.31 rows=1 width=16) (actual time=20.005..30.851 rows=2,707 loops=1)

93. 25.109 25.109 ↓ 2,713.0 2,713 1

CTE Scan on score_results score_results_2 (cost=0.00..2.00 rows=1 width=12) (actual time=19.984..25.109 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
94. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_2 (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = score_results_2.game_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 0
95. 5.414 5.414 ↑ 1.0 1 2,707

Index Scan using games_pk on games games_2 (cost=0.28..16.60 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=2,707)

  • Index Cond: (id = score_results_2.game_id)
  • Filter: ((NOT is_cancelled) AND ((game_date < '2020-10-12'::date) OR (SubPlan 10)))
96.          

SubPlan (for Index Scan)

97. 0.000 0.000 ↓ 0.0 0

Index Scan using teams_pk on teams teams_2 (cost=0.28..8.30 rows=1 width=1) (never executed)

  • Index Cond: (id = games_2.home_team)
98. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams home_team_data (cost=0.28..0.34 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_2.home_team)
99. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams away_team_data (cost=0.28..0.34 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_2.away_team)
100.          

CTE ranked_ages

101. 0.013 0.013 ↑ 1.0 15 1

Values Scan on ""*VALUES*"" (cost=0.00..0.19 rows=15 width=36) (actual time=0.003..0.013 rows=15 loops=1)

102.          

CTE all_teams

103. 2.159 57.433 ↓ 2,707.0 5,414 1

Append (cost=0.00..0.05 rows=2 width=156) (actual time=31.874..57.433 rows=5,414 loops=1)

104. 54.148 54.148 ↓ 2,707.0 2,707 1

CTE Scan on game_info (cost=0.00..0.02 rows=1 width=156) (actual time=31.872..54.148 rows=2,707 loops=1)

105. 1.126 1.126 ↓ 2,707.0 2,707 1

CTE Scan on game_info game_info_1 (cost=0.00..0.02 rows=1 width=156) (actual time=0.002..1.126 rows=2,707 loops=1)

106.          

CTE team_points

107. 67.568 88.596 ↓ 2,707.0 5,414 1

CTE Scan on all_teams (cost=0.00..2.79 rows=2 width=16) (actual time=31.919..88.596 rows=5,414 loops=1)

108.          

SubPlan (for CTE Scan)

109. 5.298 5.298 ↑ 1.0 1 2,649

CTE Scan on ranked_ages (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,649)

  • Filter: (name = all_teams.team_age_group)
  • Rows Removed by Filter: 14
110. 5.298 5.298 ↑ 1.0 1 2,649

CTE Scan on ranked_ages ranked_ages_1 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,649)

  • Filter: (name = all_teams.opponent_age_group)
  • Rows Removed by Filter: 14
111. 5.216 5.216 ↑ 1.0 1 2,608

CTE Scan on ranked_ages ranked_ages_2 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,608)

  • Filter: (name = all_teams.team_age_group)
  • Rows Removed by Filter: 14
112. 5.216 5.216 ↑ 1.0 1 2,608

CTE Scan on ranked_ages ranked_ages_3 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,608)

  • Filter: (name = all_teams.opponent_age_group)
  • Rows Removed by Filter: 14
113. 2.095 113.842 ↓ 5,102.0 5,102 1

Subquery Scan on sub (cost=0.05..0.11 rows=1 width=8) (actual time=95.347..113.842 rows=5,102 loops=1)

  • Filter: (sub.game_number <= 12)
  • Rows Removed by Filter: 312
114. 4.334 111.747 ↓ 2,707.0 5,414 1

WindowAgg (cost=0.05..0.09 rows=2 width=24) (actual time=95.345..111.747 rows=5,414 loops=1)

115. 15.297 107.413 ↓ 2,707.0 5,414 1

Sort (cost=0.05..0.06 rows=2 width=16) (actual time=95.334..107.413 rows=5,414 loops=1)

  • Sort Key: team_points.team_id, team_points.game_date
  • Sort Method: quicksort Memory: 615kB
116. 92.116 92.116 ↓ 2,707.0 5,414 1

CTE Scan on team_points (cost=0.00..0.04 rows=2 width=16) (actual time=31.922..92.116 rows=5,414 loops=1)

117. 0.026 1,646.185 ↓ 36.0 36 1

Materialize (cost=2,890.68..2,890.74 rows=1 width=36) (actual time=1,646.086..1,646.185 rows=36 loops=1)

118. 0.075 1,646.159 ↓ 36.0 36 1

GroupAggregate (cost=2,890.68..2,890.73 rows=1 width=132) (actual time=1,646.084..1,646.159 rows=36 loops=1)

  • Group Key: pf.team_id, pf.team_name, pf.age_group, pf.skill_level
119.          

CTE game_results

120. 2.205 66.771 ↓ 2,708.0 5,416 1

Append (cost=545.99..1,112.08 rows=2 width=20) (actual time=22.490..66.771 rows=5,416 loops=1)

121. 3.066 33.448 ↓ 2,708.0 2,708 1

Nested Loop (cost=545.99..556.03 rows=1 width=20) (actual time=22.488..33.448 rows=2,708 loops=1)

122. 27.669 27.669 ↓ 2,713.0 2,713 1

CTE Scan on unioned_results (cost=545.71..547.71 rows=1 width=12) (actual time=22.465..27.669 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
123.          

CTE admin_entry

124. 0.036 0.036 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_3 (cost=0.00..1.69 rows=69 width=13) (actual time=0.014..0.036 rows=76 loops=1)

125.          

CTE self_reported_entry

126. 1.385 14.078 ↓ 134.5 2,690 1

Subquery Scan on sub_4 (cost=404.69..531.79 rows=20 width=13) (actual time=6.875..14.078 rows=2,690 loops=1)

  • Filter: (sub_4.rank = 1)
  • Rows Removed by Filter: 1,177
127. 3.356 12.693 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.873..12.693 rows=3,867 loops=1)

128. 5.183 9.337 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.866..9.337 rows=3,867 loops=1)

  • Sort Key: reported_scores_3.game_id, reported_scores_3.time_reported
  • Sort Method: quicksort Memory: 399kB
129. 2.954 4.154 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.937..4.154 rows=3,867 loops=1)

  • Group Key: reported_scores_3.game_id, reported_scores_3.home_team_runs, reported_scores_3.away_team_runs, reported_scores_3.time_reported
130. 1.200 1.200 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_3 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.007..1.200 rows=3,867 loops=1)

131.          

CTE unioned_results

132. 2.733 25.868 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=22.461..25.868 rows=2,766 loops=1)

133. 1.933 23.135 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=22.455..23.135 rows=2,766 loops=1)

  • Sort Key: admin_entry.game_id, admin_entry.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
134. 2.056 21.202 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=20.373..21.202 rows=2,766 loops=1)

  • Group Key: admin_entry.game_id, admin_entry.home_team_runs, admin_entry.away_team_runs, admin_entry.was_admin_reported
135. 2.314 19.146 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=18.294..19.146 rows=2,766 loops=1)

  • Group Key: admin_entry.game_id, admin_entry.home_team_runs, admin_entry.away_team_runs, admin_entry.was_admin_reported
136. 1.115 16.832 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.017..16.832 rows=2,766 loops=1)

137. 0.084 0.084 ↓ 1.1 76 1

CTE Scan on admin_entry (cost=0.00..1.38 rows=69 width=13) (actual time=0.016..0.084 rows=76 loops=1)

138. 15.633 15.633 ↓ 134.5 2,690 1

CTE Scan on self_reported_entry (cost=0.00..0.40 rows=20 width=13) (actual time=6.877..15.633 rows=2,690 loops=1)

139. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_3 (cost=0.28..8.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = unioned_results.game_id)
140. 3.061 31.118 ↓ 2,708.0 2,708 1

Nested Loop (cost=545.99..556.03 rows=1 width=20) (actual time=20.190..31.118 rows=2,708 loops=1)

141. 25.344 25.344 ↓ 2,713.0 2,713 1

CTE Scan on unioned_results unioned_results_1 (cost=545.71..547.71 rows=1 width=12) (actual time=20.169..25.344 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
142.          

CTE admin_entry

143. 0.031 0.031 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_4 (cost=0.00..1.69 rows=69 width=13) (actual time=0.010..0.031 rows=76 loops=1)

144.          

CTE self_reported_entry

145. 1.405 11.763 ↓ 134.5 2,690 1

Subquery Scan on sub_5 (cost=404.69..531.79 rows=20 width=13) (actual time=6.091..11.763 rows=2,690 loops=1)

  • Filter: (sub_5.rank = 1)
  • Rows Removed by Filter: 1,177
146. 3.326 10.358 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.088..10.358 rows=3,867 loops=1)

147. 2.874 7.032 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.080..7.032 rows=3,867 loops=1)

  • Sort Key: reported_scores_4.game_id, reported_scores_4.time_reported
  • Sort Method: quicksort Memory: 399kB
148. 2.972 4.158 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.960..4.158 rows=3,867 loops=1)

  • Group Key: reported_scores_4.game_id, reported_scores_4.home_team_runs, reported_scores_4.away_team_runs, reported_scores_4.time_reported
149. 1.186 1.186 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_4 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.007..1.186 rows=3,867 loops=1)

150.          

CTE unioned_results

151. 2.732 23.555 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=20.166..23.555 rows=2,766 loops=1)

152. 1.891 20.823 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=20.160..20.823 rows=2,766 loops=1)

  • Sort Key: admin_entry_1.game_id, admin_entry_1.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
153. 2.054 18.932 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=18.099..18.932 rows=2,766 loops=1)

  • Group Key: admin_entry_1.game_id, admin_entry_1.home_team_runs, admin_entry_1.away_team_runs, admin_entry_1.was_admin_reported
154. 2.340 16.878 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=16.002..16.878 rows=2,766 loops=1)

  • Group Key: admin_entry_1.game_id, admin_entry_1.home_team_runs, admin_entry_1.away_team_runs, admin_entry_1.was_admin_reported
155. 1.123 14.538 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.012..14.538 rows=2,766 loops=1)

156. 0.075 0.075 ↓ 1.1 76 1

CTE Scan on admin_entry admin_entry_1 (cost=0.00..1.38 rows=69 width=13) (actual time=0.011..0.075 rows=76 loops=1)

157. 13.340 13.340 ↓ 134.5 2,690 1

CTE Scan on self_reported_entry self_reported_entry_1 (cost=0.00..0.40 rows=20 width=13) (actual time=6.092..13.340 rows=2,690 loops=1)

158. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_4 (cost=0.28..8.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = unioned_results_1.game_id)
159.          

CTE team_game_results

160. 70.592 70.592 ↓ 2,708.0 5,416 1

CTE Scan on game_results (cost=0.00..0.04 rows=2 width=13) (actual time=22.493..70.592 rows=5,416 loops=1)

161.          

CTE temp

162. 2.496 1,541.136 ↓ 416.0 416 1

GroupAggregate (cost=622.93..622.96 rows=1 width=48) (actual time=1,537.307..1,541.136 rows=416 loops=1)

  • Group Key: t_1.age_group, t_1.skill_level, (sum(sub_7.points_awarded))
163. 8.503 1,538.640 ↓ 5,416.0 5,416 1

Sort (cost=622.93..622.94 rows=1 width=20) (actual time=1,537.279..1,538.640 rows=5,416 loops=1)

  • Sort Key: t_1.age_group, t_1.skill_level, (sum(sub_7.points_awarded))
  • Sort Method: quicksort Memory: 616kB
164. 7.531 1,530.137 ↓ 5,416.0 5,416 1

Nested Loop (cost=614.74..622.92 rows=1 width=20) (actual time=146.952..1,530.137 rows=5,416 loops=1)

  • Join Filter: (tgr.team_id = t_1.id)
165. 662.811 1,517.190 ↓ 5,416.0 5,416 1

Nested Loop (cost=614.46..614.62 rows=1 width=16) (actual time=146.941..1,517.190 rows=5,416 loops=1)

  • Join Filter: (tgr.team_id = sub_7.team_id)
  • Rows Removed by Join Filter: 2,778,408
166. 1.783 129.639 ↓ 514.0 514 1

GroupAggregate (cost=614.46..614.54 rows=1 width=12) (actual time=119.104..129.639 rows=514 loops=1)

  • Group Key: sub_7.team_id
167.          

CTE game_info

168. 3.150 74.474 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.86..611.38 rows=1 width=48) (actual time=54.994..74.474 rows=2,707 loops=1)

169. 3.139 68.617 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.58..611.04 rows=1 width=44) (actual time=54.986..68.617 rows=2,707 loops=1)

170. 1.449 62.771 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.31..610.71 rows=1 width=36) (actual time=54.974..62.771 rows=2,707 loops=1)

171. 0.958 55.908 ↓ 2,707.0 2,707 1

Hash Right Join (cost=556.03..594.09 rows=1 width=24) (actual time=54.962..55.908 rows=2,707 loops=1)

  • Hash Cond: (fg_3.game_id = g_5.id)
172.          

CTE admin_entries

173. 0.034 0.034 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_5 (cost=0.00..1.69 rows=69 width=13) (actual time=0.014..0.034 rows=76 loops=1)

174.          

CTE self_reported_entries

175. 1.409 13.321 ↓ 134.5 2,690 1

Subquery Scan on sub_6 (cost=404.69..531.79 rows=20 width=13) (actual time=6.100..13.321 rows=2,690 loops=1)

  • Filter: (sub_6.rank = 1)
  • Rows Removed by Filter: 1,177
176. 4.785 11.912 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.098..11.912 rows=3,867 loops=1)

177. 2.926 7.127 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.089..7.127 rows=3,867 loops=1)

  • Sort Key: reported_scores_5.game_id, reported_scores_5.time_reported
  • Sort Method: quicksort Memory: 399kB
178. 3.007 4.201 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.971..4.201 rows=3,867 loops=1)

  • Group Key: reported_scores_5.game_id, reported_scores_5.home_team_runs, reported_scores_5.away_team_runs, reported_scores_5.time_reported
179. 1.194 1.194 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_5 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.007..1.194 rows=3,867 loops=1)

180.          

CTE score_results

181. 3.107 46.448 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=43.055..46.448 rows=2,766 loops=1)

182. 2.684 43.341 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=42.684..43.341 rows=2,766 loops=1)

  • Sort Key: admin_entries_3.game_id, admin_entries_3.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
183. 3.763 40.657 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=39.810..40.657 rows=2,766 loops=1)

  • Group Key: admin_entries_3.game_id, admin_entries_3.home_team_runs, admin_entries_3.away_team_runs, admin_entries_3.was_admin_reported
184. 20.453 36.894 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=18.358..36.894 rows=2,766 loops=1)

  • Group Key: admin_entries_3.game_id, admin_entries_3.home_team_runs, admin_entries_3.away_team_runs, admin_entries_3.was_admin_reported
185. 1.326 16.441 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.016..16.441 rows=2,766 loops=1)

186. 0.080 0.080 ↓ 1.1 76 1

CTE Scan on admin_entries admin_entries_3 (cost=0.00..1.38 rows=69 width=13) (actual time=0.015..0.080 rows=76 loops=1)

187. 15.035 15.035 ↓ 134.5 2,690 1

CTE Scan on self_reported_entries self_reported_entries_3 (cost=0.00..0.40 rows=20 width=13) (actual time=6.102..15.035 rows=2,690 loops=1)

188. 0.008 0.008 ↑ 2,040.0 1 1

Seq Scan on forfeited_games fg_3 (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.007..0.008 rows=1 loops=1)

189. 0.962 54.942 ↓ 2,707.0 2,707 1

Hash (cost=10.31..10.31 rows=1 width=16) (actual time=54.942..54.942 rows=2,707 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 159kB
190. 3.056 53.980 ↓ 2,707.0 2,707 1

Nested Loop (cost=0.28..10.31 rows=1 width=16) (actual time=43.081..53.980 rows=2,707 loops=1)

191. 48.211 48.211 ↓ 2,713.0 2,713 1

CTE Scan on score_results score_results_3 (cost=0.00..2.00 rows=1 width=12) (actual time=43.058..48.211 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
192. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_5 (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = score_results_3.game_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 0
193. 5.414 5.414 ↑ 1.0 1 2,707

Index Scan using games_pk on games games_3 (cost=0.28..16.60 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=2,707)

  • Index Cond: (id = score_results_3.game_id)
  • Filter: ((NOT is_cancelled) AND ((game_date < '2020-10-12'::date) OR (SubPlan 30)))
194.          

SubPlan (for Index Scan)

195. 0.000 0.000 ↓ 0.0 0

Index Scan using teams_pk on teams teams_3 (cost=0.28..8.30 rows=1 width=1) (never executed)

  • Index Cond: (id = games_3.home_team)
196. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams home_team_data_1 (cost=0.28..0.34 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_3.home_team)
197. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams away_team_data_1 (cost=0.28..0.34 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_3.away_team)
198.          

CTE ranked_ages

199. 0.015 0.015 ↑ 1.0 15 1

Values Scan on ""*VALUES*_1"" (cost=0.00..0.19 rows=15 width=36) (actual time=0.005..0.015 rows=15 loops=1)

200.          

CTE all_teams

201. 2.168 80.662 ↓ 2,707.0 5,414 1

Append (cost=0.00..0.05 rows=2 width=156) (actual time=54.999..80.662 rows=5,414 loops=1)

202. 77.338 77.338 ↓ 2,707.0 2,707 1

CTE Scan on game_info game_info_2 (cost=0.00..0.02 rows=1 width=156) (actual time=54.998..77.338 rows=2,707 loops=1)

203. 1.156 1.156 ↓ 2,707.0 2,707 1

CTE Scan on game_info game_info_3 (cost=0.00..0.02 rows=1 width=156) (actual time=0.002..1.156 rows=2,707 loops=1)

204.          

CTE team_points

205. 91.226 112.254 ↓ 2,707.0 5,414 1

CTE Scan on all_teams all_teams_1 (cost=0.00..2.79 rows=2 width=16) (actual time=55.046..112.254 rows=5,414 loops=1)

206.          

SubPlan (for CTE Scan)

207. 5.298 5.298 ↑ 1.0 1 2,649

CTE Scan on ranked_ages ranked_ages_4 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,649)

  • Filter: (name = all_teams_1.team_age_group)
  • Rows Removed by Filter: 14
208. 5.298 5.298 ↑ 1.0 1 2,649

CTE Scan on ranked_ages ranked_ages_5 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,649)

  • Filter: (name = all_teams_1.opponent_age_group)
  • Rows Removed by Filter: 14
209. 5.216 5.216 ↑ 1.0 1 2,608

CTE Scan on ranked_ages ranked_ages_6 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,608)

  • Filter: (name = all_teams_1.team_age_group)
  • Rows Removed by Filter: 14
210. 5.216 5.216 ↑ 1.0 1 2,608

CTE Scan on ranked_ages ranked_ages_7 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,608)

  • Filter: (name = all_teams_1.opponent_age_group)
  • Rows Removed by Filter: 14
211. 2.190 127.856 ↓ 5,102.0 5,102 1

Subquery Scan on sub_7 (cost=0.05..0.11 rows=1 width=8) (actual time=119.075..127.856 rows=5,102 loops=1)

  • Filter: (sub_7.game_number <= 12)
  • Rows Removed by Filter: 312
212. 4.778 125.666 ↓ 2,707.0 5,414 1

WindowAgg (cost=0.05..0.09 rows=2 width=24) (actual time=119.073..125.666 rows=5,414 loops=1)

213. 5.109 120.888 ↓ 2,707.0 5,414 1

Sort (cost=0.05..0.06 rows=2 width=16) (actual time=119.061..120.888 rows=5,414 loops=1)

  • Sort Key: team_points_1.team_id, team_points_1.game_date
  • Sort Method: quicksort Memory: 615kB
214. 115.779 115.779 ↓ 2,707.0 5,414 1

CTE Scan on team_points team_points_1 (cost=0.00..0.04 rows=2 width=16) (actual time=55.048..115.779 rows=5,414 loops=1)

215. 724.740 724.740 ↓ 2,708.0 5,416 514

CTE Scan on team_game_results tgr (cost=0.00..0.04 rows=2 width=4) (actual time=0.044..1.410 rows=5,416 loops=514)

216. 5.416 5.416 ↑ 1.0 1 5,416

Index Scan using teams_pk on teams t_1 (cost=0.28..8.29 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5,416)

  • Index Cond: (id = sub_7.team_id)
217.          

CTE prepped

218. 9.106 1,560.185 ↓ 13.4 134 1

Nested Loop (cost=0.00..43.02 rows=10 width=126) (actual time=1,537.523..1,560.185 rows=134 loops=1)

  • Join Filter: (teams_4.id = ANY (temp.teams))
  • Rows Removed by Join Filter: 34,974
219. 1,541.431 1,541.431 ↓ 67.0 67 1

CTE Scan on temp (cost=0.00..0.03 rows=1 width=104) (actual time=1,537.346..1,541.431 rows=67 loops=1)

  • Filter: (cardinality(teams) = 2)
  • Rows Removed by Filter: 349
220. 9.648 9.648 ↓ 1.0 524 67

Seq Scan on teams teams_4 (cost=0.00..31.23 rows=523 width=22) (actual time=0.001..0.144 rows=524 loops=67)

221.          

CTE prep_final

222. 0.230 1,645.333 ↓ 36.0 36 1

Hash Join (cost=1,112.27..1,112.55 rows=1 width=156) (actual time=1,624.397..1,645.333 rows=36 loops=1)

  • Hash Cond: (p.team_id = ththr.team_id)
  • Join Filter: (ththr.opponent_team_id = ANY (p.teams))
  • Rows Removed by Join Filter: 859
223. 1,560.313 1,560.313 ↓ 13.4 134 1

CTE Scan on prepped p (cost=0.00..0.20 rows=10 width=140) (actual time=1,537.525..1,560.313 rows=134 loops=1)

224. 1.107 84.790 ↓ 1,728.0 3,456 1

Hash (cost=1,112.24..1,112.24 rows=2 width=24) (actual time=84.789..84.790 rows=3,456 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 221kB
225. 1.362 83.683 ↓ 1,728.0 3,456 1

Subquery Scan on ththr (cost=1,112.18..1,112.24 rows=2 width=24) (actual time=78.544..83.683 rows=3,456 loops=1)

226. 2.665 82.321 ↓ 1,728.0 3,456 1

GroupAggregate (cost=1,112.18..1,112.22 rows=2 width=24) (actual time=78.542..82.321 rows=3,456 loops=1)

  • Group Key: team_game_results.team_id, team_game_results.opponent_team_id
227.          

CTE game_results

228. 2.254 68.055 ↓ 2,708.0 5,416 1

Append (cost=545.99..1,112.08 rows=2 width=20) (actual time=20.787..68.055 rows=5,416 loops=1)

229. 2.975 31.756 ↓ 2,708.0 2,708 1

Nested Loop (cost=545.99..556.03 rows=1 width=20) (actual time=20.786..31.756 rows=2,708 loops=1)

230. 26.068 26.068 ↓ 2,713.0 2,713 1

CTE Scan on unioned_results unioned_results_2 (cost=545.71..547.71 rows=1 width=12) (actual time=20.766..26.068 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
231.          

CTE admin_entry

232. 0.026 0.026 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_6 (cost=0.00..1.69 rows=69 width=13) (actual time=0.005..0.026 rows=76 loops=1)

233.          

CTE self_reported_entry

234. 1.395 12.277 ↓ 134.5 2,690 1

Subquery Scan on sub_8 (cost=404.69..531.79 rows=20 width=13) (actual time=6.708..12.277 rows=2,690 loops=1)

  • Filter: (sub_8.rank = 1)
  • Rows Removed by Filter: 1,177
235. 3.304 10.882 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.706..10.882 rows=3,867 loops=1)

236. 3.392 7.578 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.697..7.578 rows=3,867 loops=1)

  • Sort Key: reported_scores_6.game_id, reported_scores_6.time_reported
  • Sort Method: quicksort Memory: 399kB
237. 2.984 4.186 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.976..4.186 rows=3,867 loops=1)

  • Group Key: reported_scores_6.game_id, reported_scores_6.home_team_runs, reported_scores_6.away_team_runs, reported_scores_6.time_reported
238. 1.202 1.202 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_6 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.009..1.202 rows=3,867 loops=1)

239.          

CTE unioned_results

240. 2.785 24.203 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=20.762..24.203 rows=2,766 loops=1)

241. 1.951 21.418 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=20.755..21.418 rows=2,766 loops=1)

  • Sort Key: admin_entry_2.game_id, admin_entry_2.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
242. 2.083 19.467 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=18.626..19.467 rows=2,766 loops=1)

  • Group Key: admin_entry_2.game_id, admin_entry_2.home_team_runs, admin_entry_2.away_team_runs, admin_entry_2.was_admin_reported
243. 2.391 17.384 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=16.471..17.384 rows=2,766 loops=1)

  • Group Key: admin_entry_2.game_id, admin_entry_2.home_team_runs, admin_entry_2.away_team_runs, admin_entry_2.was_admin_reported
244. 1.091 14.993 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.007..14.993 rows=2,766 loops=1)

245. 0.069 0.069 ↓ 1.1 76 1

CTE Scan on admin_entry admin_entry_2 (cost=0.00..1.38 rows=69 width=13) (actual time=0.006..0.069 rows=76 loops=1)

246. 13.833 13.833 ↓ 134.5 2,690 1

CTE Scan on self_reported_entry self_reported_entry_2 (cost=0.00..0.40 rows=20 width=13) (actual time=6.709..13.833 rows=2,690 loops=1)

247. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_6 (cost=0.28..8.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = unioned_results_2.game_id)
248. 3.083 34.045 ↓ 2,708.0 2,708 1

Nested Loop (cost=545.99..556.03 rows=1 width=20) (actual time=23.001..34.045 rows=2,708 loops=1)

249. 28.249 28.249 ↓ 2,713.0 2,713 1

CTE Scan on unioned_results unioned_results_3 (cost=545.71..547.71 rows=1 width=12) (actual time=22.976..28.249 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
250.          

CTE admin_entry

251. 0.030 0.030 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_7 (cost=0.00..1.69 rows=69 width=13) (actual time=0.009..0.030 rows=76 loops=1)

252.          

CTE self_reported_entry

253. 1.438 12.276 ↓ 134.5 2,690 1

Subquery Scan on sub_9 (cost=404.69..531.79 rows=20 width=13) (actual time=6.567..12.276 rows=2,690 loops=1)

  • Filter: (sub_9.rank = 1)
  • Rows Removed by Filter: 1,177
254. 3.818 10.838 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.565..10.838 rows=3,867 loops=1)

255. 2.796 7.020 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.114..7.020 rows=3,867 loops=1)

  • Sort Key: reported_scores_7.game_id, reported_scores_7.time_reported
  • Sort Method: quicksort Memory: 399kB
256. 3.008 4.224 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=3.019..4.224 rows=3,867 loops=1)

  • Group Key: reported_scores_7.game_id, reported_scores_7.home_team_runs, reported_scores_7.away_team_runs, reported_scores_7.time_reported
257. 1.216 1.216 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_7 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.006..1.216 rows=3,867 loops=1)

258.          

CTE unioned_results

259. 2.781 26.414 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=22.973..26.414 rows=2,766 loops=1)

260. 1.932 23.633 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=22.967..23.633 rows=2,766 loops=1)

  • Sort Key: admin_entry_3.game_id, admin_entry_3.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
261. 2.053 21.701 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=20.859..21.701 rows=2,766 loops=1)

  • Group Key: admin_entry_3.game_id, admin_entry_3.home_team_runs, admin_entry_3.away_team_runs, admin_entry_3.was_admin_reported
262. 2.462 19.648 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=18.685..19.648 rows=2,766 loops=1)

  • Group Key: admin_entry_3.game_id, admin_entry_3.home_team_runs, admin_entry_3.away_team_runs, admin_entry_3.was_admin_reported
263. 2.711 17.186 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.012..17.186 rows=2,766 loops=1)

264. 0.074 0.074 ↓ 1.1 76 1

CTE Scan on admin_entry admin_entry_3 (cost=0.00..1.38 rows=69 width=13) (actual time=0.011..0.074 rows=76 loops=1)

265. 14.401 14.401 ↓ 134.5 2,690 1

CTE Scan on self_reported_entry self_reported_entry_3 (cost=0.00..0.40 rows=20 width=13) (actual time=6.569..14.401 rows=2,690 loops=1)

266. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_7 (cost=0.28..8.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = unioned_results_3.game_id)
267.          

CTE team_game_results

268. 71.848 71.848 ↓ 2,708.0 5,416 1

CTE Scan on game_results game_results_1 (cost=0.00..0.04 rows=2 width=13) (actual time=20.790..71.848 rows=5,416 loops=1)

269. 4.053 79.656 ↓ 2,708.0 5,416 1

Sort (cost=0.05..0.06 rows=2 width=9) (actual time=78.533..79.656 rows=5,416 loops=1)

  • Sort Key: team_game_results.team_id, team_game_results.opponent_team_id
  • Sort Method: quicksort Memory: 446kB
270. 75.603 75.603 ↓ 2,708.0 5,416 1

CTE Scan on team_game_results (cost=0.00..0.04 rows=2 width=9) (actual time=20.792..75.603 rows=5,416 loops=1)

271. 0.711 1,646.084 ↓ 36.0 36 1

Sort (cost=0.03..0.04 rows=1 width=116) (actual time=1,646.072..1,646.084 rows=36 loops=1)

  • Sort Key: pf.team_id, pf.team_name, pf.age_group, pf.skill_level
  • Sort Method: quicksort Memory: 28kB
272. 1,645.373 1,645.373 ↓ 36.0 36 1

CTE Scan on prep_final pf (cost=0.00..0.02 rows=1 width=116) (actual time=1,624.399..1,645.373 rows=36 loops=1)

273. 0.257 158.559 ↓ 257.0 514 1

Materialize (cost=1,810.88..1,810.97 rows=2 width=36) (actual time=154.541..158.559 rows=514 loops=1)

274. 2.292 158.302 ↓ 257.0 514 1

GroupAggregate (cost=1,810.88..1,810.94 rows=2 width=44) (actual time=154.538..158.302 rows=514 loops=1)

  • Group Key: nr.team_id, gc_1.num_games
275.          

CTE game_counts

276. 1.454 84.267 ↓ 257.0 514 1

GroupAggregate (cost=1,221.45..1,221.49 rows=2 width=12) (actual time=81.644..84.267 rows=514 loops=1)

  • Group Key: games_4.home_team
277. 3.416 82.813 ↓ 2,707.0 5,414 1

Sort (cost=1,221.45..1,221.46 rows=2 width=4) (actual time=81.629..82.813 rows=5,414 loops=1)

  • Sort Key: games_4.home_team
  • Sort Method: quicksort Memory: 446kB
278. 2.201 79.397 ↓ 2,707.0 5,414 1

Append (cost=556.31..1,221.44 rows=2 width=4) (actual time=32.102..79.397 rows=5,414 loops=1)

279. 3.090 38.659 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.31..610.71 rows=1 width=4) (actual time=32.101..38.659 rows=2,707 loops=1)

280. 0.782 32.862 ↓ 2,707.0 2,707 1

Hash Right Join (cost=556.03..594.09 rows=1 width=24) (actual time=32.093..32.862 rows=2,707 loops=1)

  • Hash Cond: (fg_4.game_id = g_8.id)
281.          

CTE admin_entries

282. 0.027 0.027 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_8 (cost=0.00..1.69 rows=69 width=13) (actual time=0.007..0.027 rows=76 loops=1)

283.          

CTE self_reported_entries

284. 1.391 11.742 ↓ 134.5 2,690 1

Subquery Scan on sub_10 (cost=404.69..531.79 rows=20 width=13) (actual time=6.093..11.742 rows=2,690 loops=1)

  • Filter: (sub_10.rank = 1)
  • Rows Removed by Filter: 1,177
285. 3.354 10.351 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.092..10.351 rows=3,867 loops=1)

286. 2.806 6.997 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.081..6.997 rows=3,867 loops=1)

  • Sort Key: reported_scores_8.game_id, reported_scores_8.time_reported
  • Sort Method: quicksort Memory: 399kB
287. 2.994 4.191 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.978..4.191 rows=3,867 loops=1)

  • Group Key: reported_scores_8.game_id, reported_scores_8.home_team_runs, reported_scores_8.away_team_runs, reported_scores_8.time_reported
288. 1.197 1.197 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_8 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.005..1.197 rows=3,867 loops=1)

289.          

CTE score_results

290. 2.761 23.608 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=20.214..23.608 rows=2,766 loops=1)

291. 1.925 20.847 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=20.209..20.847 rows=2,766 loops=1)

  • Sort Key: admin_entries_4.game_id, admin_entries_4.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
292. 2.079 18.922 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=18.081..18.922 rows=2,766 loops=1)

  • Group Key: admin_entries_4.game_id, admin_entries_4.home_team_runs, admin_entries_4.away_team_runs, admin_entries_4.was_admin_reported
293. 2.359 16.843 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=15.968..16.843 rows=2,766 loops=1)

  • Group Key: admin_entries_4.game_id, admin_entries_4.home_team_runs, admin_entries_4.away_team_runs, admin_entries_4.was_admin_reported
294. 1.105 14.484 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.009..14.484 rows=2,766 loops=1)

295. 0.071 0.071 ↓ 1.1 76 1

CTE Scan on admin_entries admin_entries_4 (cost=0.00..1.38 rows=69 width=13) (actual time=0.008..0.071 rows=76 loops=1)

296. 13.308 13.308 ↓ 134.5 2,690 1

CTE Scan on self_reported_entries self_reported_entries_4 (cost=0.00..0.40 rows=20 width=13) (actual time=6.095..13.308 rows=2,690 loops=1)

297. 0.008 0.008 ↑ 2,040.0 1 1

Seq Scan on forfeited_games fg_4 (cost=0.00..30.40 rows=2,040 width=4) (actual time=0.007..0.008 rows=1 loops=1)

298. 0.945 32.072 ↓ 2,707.0 2,707 1

Hash (cost=10.31..10.31 rows=1 width=8) (actual time=32.072..32.072 rows=2,707 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 138kB
299. 3.020 31.127 ↓ 2,707.0 2,707 1

Nested Loop (cost=0.28..10.31 rows=1 width=8) (actual time=20.238..31.127 rows=2,707 loops=1)

300. 25.394 25.394 ↓ 2,713.0 2,713 1

CTE Scan on score_results score_results_4 (cost=0.00..2.00 rows=1 width=4) (actual time=20.217..25.394 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
301. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_8 (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = score_results_4.game_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 0
302. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using games_pk on games games_4 (cost=0.28..16.60 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = score_results_4.game_id)
  • Filter: ((NOT is_cancelled) AND ((game_date < '2020-10-12'::date) OR (SubPlan 53)))
303.          

SubPlan (for Index Scan)

304. 0.000 0.000 ↓ 0.0 0

Index Scan using teams_pk on teams teams_5 (cost=0.28..8.30 rows=1 width=1) (never executed)

  • Index Cond: (id = games_4.home_team)
305. 3.088 38.537 ↓ 2,707.0 2,707 1

Nested Loop (cost=556.31..610.71 rows=1 width=4) (actual time=31.994..38.537 rows=2,707 loops=1)

306. 0.769 32.742 ↓ 2,707.0 2,707 1

Hash Right Join (cost=556.03..594.09 rows=1 width=24) (actual time=31.984..32.742 rows=2,707 loops=1)

  • Hash Cond: (fg_5.game_id = g_9.id)
307.          

CTE admin_entries

308. 0.028 0.028 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_9 (cost=0.00..1.69 rows=69 width=13) (actual time=0.006..0.028 rows=76 loops=1)

309.          

CTE self_reported_entries

310. 1.424 11.838 ↓ 134.5 2,690 1

Subquery Scan on sub_11 (cost=404.69..531.79 rows=20 width=13) (actual time=6.226..11.838 rows=2,690 loops=1)

  • Filter: (sub_11.rank = 1)
  • Rows Removed by Filter: 1,177
311. 3.305 10.414 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.223..10.414 rows=3,867 loops=1)

312. 2.799 7.109 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.215..7.109 rows=3,867 loops=1)

  • Sort Key: reported_scores_9.game_id, reported_scores_9.time_reported
  • Sort Method: quicksort Memory: 399kB
313. 3.053 4.310 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=3.101..4.310 rows=3,867 loops=1)

  • Group Key: reported_scores_9.game_id, reported_scores_9.home_team_runs, reported_scores_9.away_team_runs, reported_scores_9.time_reported
314. 1.257 1.257 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_9 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.007..1.257 rows=3,867 loops=1)

315.          

CTE score_results

316. 2.676 23.539 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=20.216..23.539 rows=2,766 loops=1)

317. 1.903 20.863 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=20.210..20.863 rows=2,766 loops=1)

  • Sort Key: admin_entries_5.game_id, admin_entries_5.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
318. 2.056 18.960 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=18.126..18.960 rows=2,766 loops=1)

  • Group Key: admin_entries_5.game_id, admin_entries_5.home_team_runs, admin_entries_5.away_team_runs, admin_entries_5.was_admin_reported
319. 2.322 16.904 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=16.042..16.904 rows=2,766 loops=1)

  • Group Key: admin_entries_5.game_id, admin_entries_5.home_team_runs, admin_entries_5.away_team_runs, admin_entries_5.was_admin_reported
320. 1.104 14.582 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.008..14.582 rows=2,766 loops=1)

321. 0.074 0.074 ↓ 1.1 76 1

CTE Scan on admin_entries admin_entries_5 (cost=0.00..1.38 rows=69 width=13) (actual time=0.008..0.074 rows=76 loops=1)

322. 13.404 13.404 ↓ 134.5 2,690 1

CTE Scan on self_reported_entries self_reported_entries_5 (cost=0.00..0.40 rows=20 width=13) (actual time=6.227..13.404 rows=2,690 loops=1)

323. 0.007 0.007 ↑ 2,040.0 1 1

Seq Scan on forfeited_games fg_5 (cost=0.00..30.40 rows=2,040 width=4) (actual time=0.006..0.007 rows=1 loops=1)

324. 0.914 31.966 ↓ 2,707.0 2,707 1

Hash (cost=10.31..10.31 rows=1 width=8) (actual time=31.965..31.966 rows=2,707 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 138kB
325. 3.082 31.052 ↓ 2,707.0 2,707 1

Nested Loop (cost=0.28..10.31 rows=1 width=8) (actual time=20.238..31.052 rows=2,707 loops=1)

326. 25.257 25.257 ↓ 2,713.0 2,713 1

CTE Scan on score_results score_results_5 (cost=0.00..2.00 rows=1 width=4) (actual time=20.219..25.257 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
327. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games g_9 (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = score_results_5.game_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 0
328. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using games_pk on games games_5 (cost=0.28..16.60 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = score_results_5.game_id)
  • Filter: ((NOT is_cancelled) AND ((game_date < '2020-10-12'::date) OR (SubPlan 57)))
329.          

SubPlan (for Index Scan)

330. 0.000 0.000 ↓ 0.0 0

Index Scan using teams_pk on teams teams_6 (cost=0.28..8.30 rows=1 width=1) (never executed)

  • Index Cond: (id = games_5.home_team)
331.          

CTE game_info

332. 10.826 55.264 ↓ 2,707.0 2,707 1

Nested Loop (cost=545.99..589.20 rows=1 width=148) (actual time=20.027..55.264 rows=2,707 loops=1)

333. 25.483 25.483 ↓ 2,713.0 2,713 1

CTE Scan on unioned_results unioned_results_4 (cost=545.71..547.71 rows=1 width=12) (actual time=19.977..25.483 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
334.          

CTE admin_entry

335. 0.027 0.027 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_10 (cost=0.00..1.69 rows=69 width=13) (actual time=0.006..0.027 rows=76 loops=1)

336.          

CTE self_reported_entry

337. 1.402 11.612 ↓ 134.5 2,690 1

Subquery Scan on sub_12 (cost=404.69..531.79 rows=20 width=13) (actual time=6.049..11.612 rows=2,690 loops=1)

  • Filter: (sub_12.rank = 1)
  • Rows Removed by Filter: 1,177
338. 3.312 10.210 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.046..10.210 rows=3,867 loops=1)

339. 2.730 6.898 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.039..6.898 rows=3,867 loops=1)

  • Sort Key: reported_scores_10.game_id, reported_scores_10.time_reported
  • Sort Method: quicksort Memory: 399kB
340. 2.983 4.168 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.959..4.168 rows=3,867 loops=1)

  • Group Key: reported_scores_10.game_id, reported_scores_10.home_team_runs, reported_scores_10.away_team_runs, reported_scores_10.time_reported
341. 1.185 1.185 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_10 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.006..1.185 rows=3,867 loops=1)

342.          

CTE unioned_results

343. 2.889 23.644 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=19.974..23.644 rows=2,766 loops=1)

344. 2.021 20.755 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=19.968..20.755 rows=2,766 loops=1)

  • Sort Key: admin_entry_4.game_id, admin_entry_4.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
345. 2.060 18.734 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=17.901..18.734 rows=2,766 loops=1)

  • Group Key: admin_entry_4.game_id, admin_entry_4.home_team_runs, admin_entry_4.away_team_runs, admin_entry_4.was_admin_reported
346. 2.319 16.674 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=15.836..16.674 rows=2,766 loops=1)

  • Group Key: admin_entry_4.game_id, admin_entry_4.home_team_runs, admin_entry_4.away_team_runs, admin_entry_4.was_admin_reported
347. 1.108 14.355 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.009..14.355 rows=2,766 loops=1)

348. 0.072 0.072 ↓ 1.1 76 1

CTE Scan on admin_entry admin_entry_4 (cost=0.00..1.38 rows=69 width=13) (actual time=0.008..0.072 rows=76 loops=1)

349. 13.175 13.175 ↓ 134.5 2,690 1

CTE Scan on self_reported_entry self_reported_entry_4 (cost=0.00..0.40 rows=20 width=13) (actual time=6.050..13.175 rows=2,690 loops=1)

350. 2.713 2.713 ↑ 1.0 1 2,713

Index Scan using games_pk on games games_6 (cost=0.28..8.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,713)

  • Index Cond: (id = unioned_results_4.game_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 0
351.          

SubPlan (for Nested Loop)

352. 5.414 5.414 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_7 (cost=0.28..8.29 rows=1 width=2) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Index Cond: (id = games_6.home_team)
353. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_8 (cost=0.28..8.29 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_6.home_team)
354. 5.414 5.414 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_9 (cost=0.28..8.29 rows=1 width=2) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Index Cond: (id = games_6.away_team)
355. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_10 (cost=0.28..8.29 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_6.away_team)
356.          

CTE normalized_runs

357. 2.082 60.656 ↓ 2,707.0 5,414 1

Append (cost=0.00..0.05 rows=2 width=12) (actual time=20.031..60.656 rows=5,414 loops=1)

358. 57.652 57.652 ↓ 2,707.0 2,707 1

CTE Scan on game_info game_info_4 (cost=0.00..0.02 rows=1 width=12) (actual time=20.029..57.652 rows=2,707 loops=1)

359. 0.922 0.922 ↓ 2,707.0 2,707 1

CTE Scan on game_info game_info_5 (cost=0.00..0.02 rows=1 width=12) (actual time=0.002..0.922 rows=2,707 loops=1)

360. 4.174 156.010 ↓ 2,707.0 5,414 1

Sort (cost=0.14..0.15 rows=2 width=16) (actual time=154.512..156.010 rows=5,414 loops=1)

  • Sort Key: nr.team_id, gc_1.num_games
  • Sort Method: quicksort Memory: 615kB
361. 3.161 151.836 ↓ 2,707.0 5,414 1

Hash Join (cost=0.07..0.13 rows=2 width=16) (actual time=104.783..151.836 rows=5,414 loops=1)

  • Hash Cond: (nr.team_id = gc_1.team_id)
362. 63.935 63.935 ↓ 2,707.0 5,414 1

CTE Scan on normalized_runs nr (cost=0.00..0.04 rows=2 width=8) (actual time=20.033..63.935 rows=5,414 loops=1)

363. 0.163 84.740 ↓ 257.0 514 1

Hash (cost=0.04..0.04 rows=2 width=12) (actual time=84.740..84.740 rows=514 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
364. 84.577 84.577 ↓ 257.0 514 1

CTE Scan on game_counts gc_1 (cost=0.00..0.04 rows=2 width=12) (actual time=81.646..84.577 rows=514 loops=1)

365. 0.688 160.508 ↓ 257.0 514 1

GroupAggregate (cost=603.47..603.56 rows=2 width=36) (actual time=159.077..160.508 rows=514 loops=1)

  • Group Key: tp.team_id
366.          

CTE game_info

367. 9.111 56.738 ↓ 2,707.0 2,707 1

Nested Loop (cost=545.99..597.50 rows=1 width=148) (actual time=20.317..56.738 rows=2,707 loops=1)

368. 25.959 25.959 ↓ 2,713.0 2,713 1

CTE Scan on unioned_results unioned_results_5 (cost=545.71..547.71 rows=1 width=12) (actual time=20.262..25.959 rows=2,713 loops=1)

  • Filter: (rk = 1)
  • Rows Removed by Filter: 53
369.          

CTE admin_entry

370. 0.035 0.035 ↓ 1.1 76 1

Seq Scan on admin_reported_scores admin_reported_scores_11 (cost=0.00..1.69 rows=69 width=13) (actual time=0.014..0.035 rows=76 loops=1)

371.          

CTE self_reported_entry

372. 1.416 11.809 ↓ 134.5 2,690 1

Subquery Scan on sub_13 (cost=404.69..531.79 rows=20 width=13) (actual time=6.048..11.809 rows=2,690 loops=1)

  • Filter: (sub_13.rank = 1)
  • Rows Removed by Filter: 1,177
373. 3.444 10.393 ↑ 1.0 3,867 1

WindowAgg (cost=404.69..482.91 rows=3,911 width=28) (actual time=6.046..10.393 rows=3,867 loops=1)

374. 2.800 6.949 ↑ 1.0 3,867 1

Sort (cost=404.69..414.46 rows=3,911 width=20) (actual time=6.037..6.949 rows=3,867 loops=1)

  • Sort Key: reported_scores_11.game_id, reported_scores_11.time_reported
  • Sort Method: quicksort Memory: 399kB
375. 2.938 4.149 ↑ 1.0 3,867 1

HashAggregate (cost=132.22..171.33 rows=3,911 width=20) (actual time=2.943..4.149 rows=3,867 loops=1)

  • Group Key: reported_scores_11.game_id, reported_scores_11.home_team_runs, reported_scores_11.away_team_runs, reported_scores_11.time_reported
376. 1.211 1.211 ↑ 1.0 3,867 1

Seq Scan on reported_scores reported_scores_11 (cost=0.00..93.11 rows=3,911 width=20) (actual time=0.007..1.211 rows=3,867 loops=1)

377.          

CTE unioned_results

378. 2.970 24.036 ↓ 31.1 2,766 1

WindowAgg (cost=10.45..12.23 rows=89 width=21) (actual time=20.259..24.036 rows=2,766 loops=1)

379. 2.087 21.066 ↓ 31.1 2,766 1

Sort (cost=10.45..10.67 rows=89 width=13) (actual time=20.252..21.066 rows=2,766 loops=1)

  • Sort Key: admin_entry_5.game_id, admin_entry_5.was_admin_reported DESC
  • Sort Method: quicksort Memory: 226kB
380. 2.093 18.979 ↓ 31.1 2,766 1

HashAggregate (cost=6.67..7.56 rows=89 width=13) (actual time=18.137..18.979 rows=2,766 loops=1)

  • Group Key: admin_entry_5.game_id, admin_entry_5.home_team_runs, admin_entry_5.away_team_runs, admin_entry_5.was_admin_reported
381. 2.310 16.886 ↓ 31.1 2,766 1

HashAggregate (cost=4.00..4.89 rows=89 width=13) (actual time=16.033..16.886 rows=2,766 loops=1)

  • Group Key: admin_entry_5.game_id, admin_entry_5.home_team_runs, admin_entry_5.away_team_runs, admin_entry_5.was_admin_reported
382. 1.119 14.576 ↓ 31.1 2,766 1

Append (cost=0.00..3.12 rows=89 width=13) (actual time=0.016..14.576 rows=2,766 loops=1)

383. 0.081 0.081 ↓ 1.1 76 1

CTE Scan on admin_entry admin_entry_5 (cost=0.00..1.38 rows=69 width=13) (actual time=0.015..0.081 rows=76 loops=1)

384. 13.376 13.376 ↓ 134.5 2,690 1

CTE Scan on self_reported_entry self_reported_entry_5 (cost=0.00..0.40 rows=20 width=13) (actual time=6.050..13.376 rows=2,690 loops=1)

385. 5.426 5.426 ↑ 1.0 1 2,713

Index Scan using games_pk on games games_7 (cost=0.28..16.60 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=2,713)

  • Index Cond: (id = unioned_results_5.game_id)
  • Filter: ((NOT is_cancelled) AND (NOT is_cancelled) AND ((game_date < '2020-10-12'::date) OR (SubPlan 75)))
  • Rows Removed by Filter: 0
386.          

SubPlan (for Index Scan)

387. 0.000 0.000 ↓ 0.0 0

Index Scan using teams_pk on teams teams_15 (cost=0.28..8.30 rows=1 width=1) (never executed)

  • Index Cond: (id = games_7.home_team)
388.          

SubPlan (for Nested Loop)

389. 5.414 5.414 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_11 (cost=0.28..8.29 rows=1 width=2) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Index Cond: (id = games_7.home_team)
390. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_12 (cost=0.28..8.29 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_7.home_team)
391. 5.414 5.414 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_13 (cost=0.28..8.29 rows=1 width=2) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Index Cond: (id = games_7.away_team)
392. 2.707 2.707 ↑ 1.0 1 2,707

Index Scan using teams_pk on teams teams_14 (cost=0.28..8.29 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=2,707)

  • Index Cond: (id = games_7.away_team)
393.          

CTE ranked_ages

394. 0.014 0.014 ↑ 1.0 15 1

Values Scan on ""*VALUES*_2"" (cost=0.00..0.19 rows=15 width=36) (actual time=0.004..0.014 rows=15 loops=1)

395.          

CTE points

396. 66.650 87.678 ↓ 2,707.0 2,707 1

CTE Scan on game_info game_info_6 (cost=0.00..2.76 rows=1 width=16) (actual time=20.925..87.678 rows=2,707 loops=1)

397.          

SubPlan (for CTE Scan)

398. 2.934 2.934 ↑ 1.0 1 1,467

CTE Scan on ranked_ages ranked_ages_8 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,467)

  • Filter: (name = game_info_6.home_team_age_group)
  • Rows Removed by Filter: 14
399. 2.934 2.934 ↑ 1.0 1 1,467

CTE Scan on ranked_ages ranked_ages_9 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,467)

  • Filter: (name = game_info_6.away_team_age_group)
  • Rows Removed by Filter: 14
400. 2.888 2.888 ↑ 1.0 1 1,444

CTE Scan on ranked_ages ranked_ages_10 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,444)

  • Filter: (name = game_info_6.home_team_age_group)
  • Rows Removed by Filter: 14
401. 2.888 2.888 ↑ 1.0 1 1,444

CTE Scan on ranked_ages ranked_ages_11 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,444)

  • Filter: (name = game_info_6.away_team_age_group)
  • Rows Removed by Filter: 14
402. 2.364 2.364 ↑ 1.0 1 1,182

CTE Scan on ranked_ages ranked_ages_12 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,182)

  • Filter: (name = game_info_6.away_team_age_group)
  • Rows Removed by Filter: 14
403. 2.364 2.364 ↑ 1.0 1 1,182

CTE Scan on ranked_ages ranked_ages_13 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,182)

  • Filter: (name = game_info_6.home_team_age_group)
  • Rows Removed by Filter: 14
404. 2.328 2.328 ↑ 1.0 1 1,164

CTE Scan on ranked_ages ranked_ages_14 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,164)

  • Filter: (name = game_info_6.away_team_age_group)
  • Rows Removed by Filter: 14
405. 2.328 2.328 ↑ 1.0 1 1,164

CTE Scan on ranked_ages ranked_ages_15 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,164)

  • Filter: (name = game_info_6.home_team_age_group)
  • Rows Removed by Filter: 14
406.          

CTE max_points_by_game

407. 11.012 53.976 ↓ 2,707.0 2,707 1

CTE Scan on game_info game_info_7 (cost=0.00..2.75 rows=1 width=16) (actual time=0.039..53.976 rows=2,707 loops=1)

408.          

SubPlan (for CTE Scan)

409. 5.414 5.414 ↑ 1.0 1 2,707

CTE Scan on ranked_ages ranked_ages_16 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Filter: (name = game_info_7.home_team_age_group)
  • Rows Removed by Filter: 14
410. 5.414 5.414 ↑ 1.0 1 2,707

CTE Scan on ranked_ages ranked_ages_17 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Filter: (name = game_info_7.away_team_age_group)
  • Rows Removed by Filter: 14
411. 5.330 5.330 ↑ 1.0 1 2,665

CTE Scan on ranked_ages ranked_ages_18 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,665)

  • Filter: (name = game_info_7.home_team_age_group)
  • Rows Removed by Filter: 14
412. 5.330 5.330 ↑ 1.0 1 2,665

CTE Scan on ranked_ages ranked_ages_19 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,665)

  • Filter: (name = game_info_7.away_team_age_group)
  • Rows Removed by Filter: 14
413. 5.414 5.414 ↑ 1.0 1 2,707

CTE Scan on ranked_ages ranked_ages_20 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Filter: (name = game_info_7.away_team_age_group)
  • Rows Removed by Filter: 14
414. 5.414 5.414 ↑ 1.0 1 2,707

CTE Scan on ranked_ages ranked_ages_21 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,707)

  • Filter: (name = game_info_7.home_team_age_group)
  • Rows Removed by Filter: 14
415. 5.324 5.324 ↑ 1.0 1 2,662

CTE Scan on ranked_ages ranked_ages_22 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,662)

  • Filter: (name = game_info_7.away_team_age_group)
  • Rows Removed by Filter: 14
416. 5.324 5.324 ↑ 1.0 1 2,662

CTE Scan on ranked_ages ranked_ages_23 (cost=0.00..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,662)

  • Filter: (name = game_info_7.home_team_age_group)
  • Rows Removed by Filter: 14
417.          

CTE max_points_by_team

418. 2.084 60.578 ↓ 257.0 514 1

HashAggregate (cost=0.06..0.08 rows=2 width=12) (actual time=60.415..60.578 rows=514 loops=1)

  • Group Key: max_points_by_game.home_team
419. 2.109 58.494 ↓ 2,707.0 5,414 1

Append (cost=0.00..0.05 rows=2 width=8) (actual time=0.042..58.494 rows=5,414 loops=1)

420. 55.621 55.621 ↓ 2,707.0 2,707 1

CTE Scan on max_points_by_game (cost=0.00..0.02 rows=1 width=8) (actual time=0.041..55.621 rows=2,707 loops=1)

421. 0.764 0.764 ↓ 2,707.0 2,707 1

CTE Scan on max_points_by_game max_points_by_game_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.764 rows=2,707 loops=1)

422.          

CTE team_points

423. 2.243 97.471 ↓ 257.0 514 1

HashAggregate (cost=0.06..0.08 rows=2 width=12) (actual time=97.322..97.471 rows=514 loops=1)

  • Group Key: points.home_team
424. 2.718 95.228 ↓ 2,707.0 5,414 1

Append (cost=0.00..0.05 rows=2 width=8) (actual time=20.928..95.228 rows=5,414 loops=1)

425. 89.583 89.583 ↓ 2,707.0 2,707 1

CTE Scan on points (cost=0.00..0.02 rows=1 width=8) (actual time=20.927..89.583 rows=2,707 loops=1)

426. 2.927 2.927 ↓ 2,707.0 2,707 1

CTE Scan on points points_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..2.927 rows=2,707 loops=1)

427. 0.480 159.820 ↓ 257.0 514 1

Merge Join (cost=0.10..0.14 rows=2 width=20) (actual time=159.064..159.820 rows=514 loops=1)

  • Merge Cond: (tp.team_id = mp.team_id)
428. 0.321 98.091 ↓ 257.0 514 1

Sort (cost=0.05..0.06 rows=2 width=12) (actual time=97.951..98.091 rows=514 loops=1)

  • Sort Key: tp.team_id
  • Sort Method: quicksort Memory: 49kB
429. 97.770 97.770 ↓ 257.0 514 1

CTE Scan on team_points tp (cost=0.00..0.04 rows=2 width=12) (actual time=97.324..97.770 rows=514 loops=1)

430. 0.346 61.249 ↓ 257.0 514 1

Sort (cost=0.05..0.06 rows=2 width=12) (actual time=61.106..61.249 rows=514 loops=1)

  • Sort Key: mp.team_id
  • Sort Method: quicksort Memory: 49kB
431. 60.903 60.903 ↓ 257.0 514 1

CTE Scan on max_points_by_team mp (cost=0.00..0.04 rows=2 width=12) (actual time=60.418..60.903 rows=514 loops=1)

Planning time : 8.257 ms
Execution time : 2,191.654 ms