explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Ea8

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 3,854.096 ↓ 2.0 2 1

Unique (cost=7,135.03..7,135.04 rows=1 width=308) (actual time=3,854.069..3,854.096 rows=2 loops=1)

2. 0.018 3,854.089 ↓ 2.0 2 1

Sort (cost=7,135.03..7,135.03 rows=1 width=308) (actual time=3,854.068..3,854.089 rows=2 loops=1)

  • Sort Key: games.start_time, games.home
  • Sort Method: quicksort Memory: 25kB
3. 0.594 3,854.071 ↓ 2.0 2 1

Nested Loop (cost=5,670.24..7,135.02 rows=1 width=308) (actual time=1,807.155..3,854.071 rows=2 loops=1)

  • Join Filter: (games.away = x_2.home)
  • Rows Removed by Join Filter: 7,413
4. 8.388 3,762.757 ↓ 5.0 5 1

Nested Loop (cost=3,252.59..4,192.66 rows=1 width=166) (actual time=912.202..3,762.757 rows=5 loops=1)

  • Join Filter: (games.home = x_1.home)
  • Rows Removed by Join Filter: 96,385
5. 62.524 2,529.244 ↓ 135.0 135 1

Nested Loop (cost=834.78..1,250.18 rows=1 width=102) (actual time=642.564..2,529.244 rows=135 loops=1)

  • Join Filter: ((games."1" >= (o."1" - 0.05)) AND (games."1" <= (o."1" + 0.05)) AND (games.x >= (o.x - 0.05)) AND (games.x <= (o.x + 0.05)) AND (games."2" >= (o."2" - 0.05)) AND (games."2" <= (o."2" + 0.05)))
  • Rows Removed by Join Filter: 193,583
6. 3.540 38.532 ↓ 959.0 959 1

Group (cost=834.78..834.81 rows=1 width=184) (actual time=33.934..38.532 rows=959 loops=1)

  • Group Key: o."1", o.x, o."2", (count(*)), nobtts.bttscount
7. 6.424 34.992 ↓ 3,421.0 3,421 1

Sort (cost=834.78..834.78 rows=1 width=112) (actual time=33.922..34.992 rows=3,421 loops=1)

  • Sort Key: o."1", o.x, o."2", (count(*)), nobtts.bttscount
  • Sort Method: quicksort Memory: 364kB
8. 1.262 28.568 ↓ 3,421.0 3,421 1

Nested Loop (cost=798.09..834.77 rows=1 width=112) (actual time=16.601..28.568 rows=3,421 loops=1)

9. 2.816 20.593 ↓ 959.0 959 1

Hash Join (cost=797.80..833.71 rows=1 width=208) (actual time=16.568..20.593 rows=959 loops=1)

  • Hash Cond: (((round(odds."1", 2)) = nobtts."1") AND ((round(odds.x, 2)) = nobtts.x) AND ((round(odds."2", 2)) = nobtts."2"))
  • Join Filter: ((((100.0 * ((count(*)))::numeric) / (((count(*)) + nobtts.bttscount))::numeric) >= '50'::numeric) OR ((((100.0 * ((count(*)))::numeric) / (((count(*)) + nobtts.bttscount))::numeric) > '75'::numeric) AND (((count(*)) + nobtts.bttscount) >= 13)))
  • Rows Removed by Join Filter: 313
10. 4.117 8.394 ↓ 3.4 3,491 1

HashAggregate (cost=381.04..398.81 rows=1,015 width=104) (actual time=7.123..8.394 rows=3,491 loops=1)

  • Group Key: round(odds."1", 2), round(odds.x, 2), round(odds."2", 2)
11. 4.277 4.277 ↑ 1.0 5,315 1

Seq Scan on odds (cost=0.00..327.46 rows=5,358 width=96) (actual time=0.023..4.277 rows=5,315 loops=1)

  • Filter: (("Away Score" > 0) AND ("Home Score" > 0))
  • Rows Removed by Filter: 4,837
12. 1.354 9.383 ↓ 3.3 3,347 1

Hash (cost=399.03..399.03 rows=1,013 width=104) (actual time=9.380..9.383 rows=3,347 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 229kB
13. 0.405 8.029 ↓ 3.3 3,357 1

Subquery Scan on nobtts (cost=371.17..399.03 rows=1,013 width=104) (actual time=6.727..8.029 rows=3,357 loops=1)

14. 3.511 7.624 ↓ 3.3 3,357 1

HashAggregate (cost=371.17..388.90 rows=1,013 width=104) (actual time=6.725..7.624 rows=3,357 loops=1)

  • Group Key: round(odds_1."1", 2), round(odds_1.x, 2), round(odds_1."2", 2)
15. 4.113 4.113 ↓ 1.0 4,837 1

Seq Scan on odds odds_1 (cost=0.00..323.23 rows=4,794 width=96) (actual time=0.015..4.113 rows=4,837 loops=1)

  • Filter: (("Away Score" < 1) OR ("Home Score" < 1))
  • Rows Removed by Filter: 5,315
16. 6.713 6.713 ↓ 4.0 4 959

Index Scan using odds_odds_index on odds o (cost=0.29..1.05 rows=1 width=96) (actual time=0.005..0.007 rows=4 loops=959)

  • Index Cond: ((round("1", 2) = (round(odds."1", 2))) AND (round(x, 2) = (round(odds.x, 2))) AND (round("2", 2) = (round(odds."2", 2))))
17. 2,428.188 2,428.188 ↑ 1.1 202 959

Seq Scan on games (cost=0.00..406.72 rows=216 width=54) (actual time=1.297..2.532 rows=202 loops=959)

  • Filter: (("2" < '4'::numeric) AND (date > (now())::date))
  • Rows Removed by Filter: 12,184
18. 85.009 1,225.125 ↑ 4.9 714 135

Hash Join (cost=2,417.82..2,863.82 rows=3,496 width=138) (actual time=0.348..9.075 rows=714 loops=135)

  • Hash Cond: (x.away = x_1.home)
19. 228.825 1,112.535 ↓ 1.5 3,773 135

GroupAggregate (cost=962.87..1,377.09 rows=2,506 width=74) (actual time=0.131..8.241 rows=3,773 loops=135)

  • Group Key: x.away
20. 96.660 883.710 ↓ 1.8 5,964 135

Subquery Scan on x (cost=962.87..1,343.57 rows=3,384 width=10) (actual time=0.125..6.546 rows=5,964 loops=135)

  • Filter: (x.r <= 3)
  • Rows Removed by Filter: 4,188
21. 628.425 787.050 ↑ 1.0 10,152 135

WindowAgg (cost=962.87..1,216.67 rows=10,152 width=230) (actual time=0.124..5.830 rows=10,152 loops=135)

22. 154.781 158.625 ↑ 1.0 10,152 135

Sort (cost=962.87..988.25 rows=10,152 width=14) (actual time=0.119..1.175 rows=10,152 loops=135)

  • Sort Key: t.away, ((t.date)::date) DESC
  • Sort Method: quicksort Memory: 940kB
23. 3.844 3.844 ↑ 1.0 10,152 1

Seq Scan on odds t (cost=0.00..287.28 rows=10,152 width=14) (actual time=0.019..3.844 rows=10,152 loops=1)

24. 0.240 27.581 ↓ 3.7 1,027 1

Hash (cost=1,451.46..1,451.46 rows=279 width=74) (actual time=27.578..27.581 rows=1,027 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
25. 4.163 27.341 ↓ 3.7 1,027 1

GroupAggregate (cost=962.87..1,448.67 rows=279 width=74) (actual time=16.700..27.341 rows=1,027 loops=1)

  • Group Key: x_1.home
  • Filter: ((avg(x_1."Home Score") >= '2'::numeric) AND (avg(x_1."Away Score") >= '1'::numeric))
  • Rows Removed by Filter: 2,768
26. 0.712 23.178 ↓ 1.8 5,952 1

Subquery Scan on x_1 (cost=962.87..1,343.57 rows=3,384 width=18) (actual time=16.670..23.178 rows=5,952 loops=1)

  • Filter: (x_1.r <= 3)
  • Rows Removed by Filter: 4,200
27. 4.876 22.466 ↑ 1.0 10,152 1

WindowAgg (cost=962.87..1,216.67 rows=10,152 width=230) (actual time=16.668..22.466 rows=10,152 loops=1)

28. 13.574 17.590 ↑ 1.0 10,152 1

Sort (cost=962.87..988.25 rows=10,152 width=22) (actual time=16.650..17.590 rows=10,152 loops=1)

  • Sort Key: t_1.home, ((t_1.date)::date) DESC
  • Sort Method: quicksort Memory: 1,174kB
29. 4.016 4.016 ↑ 1.0 10,152 1

Seq Scan on odds t_1 (cost=0.00..287.28 rows=10,152 width=22) (actual time=0.025..4.016 rows=10,152 loops=1)

30. 3.957 90.720 ↑ 2.4 1,483 5

Hash Join (cost=2,417.64..2,863.78 rows=3,492 width=138) (actual time=9.049..18.144 rows=1,483 loops=5)

  • Hash Cond: (x_2.home = away_3_scores.away)
31. 8.540 58.140 ↓ 1.5 3,795 5

GroupAggregate (cost=962.87..1,377.15 rows=2,512 width=74) (actual time=3.306..11.628 rows=3,795 loops=5)

  • Group Key: x_2.home
32. 3.710 49.600 ↓ 1.8 5,952 5

Subquery Scan on x_2 (cost=962.87..1,343.57 rows=3,384 width=10) (actual time=3.300..9.920 rows=5,952 loops=5)

  • Filter: (x_2.r <= 3)
  • Rows Removed by Filter: 4,200
33. 23.950 45.890 ↑ 1.0 10,152 5

WindowAgg (cost=962.87..1,216.67 rows=10,152 width=230) (actual time=3.300..9.178 rows=10,152 loops=5)

34. 18.065 21.940 ↑ 1.0 10,152 5

Sort (cost=962.87..988.25 rows=10,152 width=14) (actual time=3.294..4.388 rows=10,152 loops=5)

  • Sort Key: t_2.home, ((t_2.date)::date) DESC
  • Sort Method: quicksort Memory: 940kB
35. 3.875 3.875 ↑ 1.0 10,152 1

Seq Scan on odds t_2 (cost=0.00..287.28 rows=10,152 width=14) (actual time=0.022..3.875 rows=10,152 loops=1)

36. 0.473 28.623 ↓ 7.4 2,049 1

Hash (cost=1,451.30..1,451.30 rows=278 width=74) (actual time=28.619..28.623 rows=2,049 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 128kB
37. 0.220 28.150 ↓ 7.4 2,049 1

Subquery Scan on away_3_scores (cost=962.87..1,451.30 rows=278 width=74) (actual time=17.163..28.150 rows=2,049 loops=1)

38. 4.304 27.930 ↓ 7.4 2,049 1

GroupAggregate (cost=962.87..1,448.52 rows=278 width=74) (actual time=17.162..27.930 rows=2,049 loops=1)

  • Group Key: x_3.away
  • Filter: ((avg(x_3."Away Score") >= '1'::numeric) AND (avg(x_3."Home Score") >= '1'::numeric))
  • Rows Removed by Filter: 1,724
39. 0.713 23.626 ↓ 1.8 5,964 1

Subquery Scan on x_3 (cost=962.87..1,343.57 rows=3,384 width=18) (actual time=17.129..23.626 rows=5,964 loops=1)

  • Filter: (x_3.r <= 3)
  • Rows Removed by Filter: 4,188
40. 4.850 22.913 ↑ 1.0 10,152 1

WindowAgg (cost=962.87..1,216.67 rows=10,152 width=230) (actual time=17.127..22.913 rows=10,152 loops=1)

41. 13.977 18.063 ↑ 1.0 10,152 1

Sort (cost=962.87..988.25 rows=10,152 width=22) (actual time=17.111..18.063 rows=10,152 loops=1)

  • Sort Key: t_3.away, ((t_3.date)::date) DESC
  • Sort Method: quicksort Memory: 1,174kB
42. 4.086 4.086 ↑ 1.0 10,152 1

Seq Scan on odds t_3 (cost=0.00..287.28 rows=10,152 width=22) (actual time=0.029..4.086 rows=10,152 loops=1)

Planning time : 1.899 ms
Execution time : 3,854.430 ms