explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5ERp

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 3,918.032 ↓ 2.0 2 1

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

2. 0.016 3,918.027 ↓ 2.0 2 1

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

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

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

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

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

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

Nested Loop (cost=834.78..1,250.18 rows=1 width=102) (actual time=601.777..2,535.899 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.621 27.921 ↓ 959.0 959 1

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

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

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

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

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

9. 1.913 14.003 ↓ 959.0 959 1

Hash Join (cost=797.80..833.71 rows=1 width=208) (actual time=11.212..14.003 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. 2.764 5.748 ↓ 3.4 3,491 1

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

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

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

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

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

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

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

14. 2.448 5.206 ↓ 3.3 3,357 1

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

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

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

  • Filter: (("Away Score" < 1) OR ("Home Score" < 1))
  • Rows Removed by Filter: 5,315
16. 4.795 4.795 ↓ 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.003..0.005 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,445.450 2,445.450 ↑ 1.1 202 959

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

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

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

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

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

  • Group Key: x.away
20. 99.630 926.775 ↓ 1.8 5,964 135

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

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

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

22. 159.982 163.890 ↑ 1.0 10,152 135

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

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

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

24. 0.248 27.376 ↓ 3.7 1,027 1

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

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

GroupAggregate (cost=962.87..1,448.67 rows=279 width=74) (actual time=16.404..27.128 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.707 22.900 ↓ 1.8 5,952 1

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

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

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

28. 13.338 17.299 ↑ 1.0 10,152 1

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

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

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

30. 3.993 91.820 ↑ 2.4 1,483 5

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

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

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

  • Group Key: x_2.home
32. 3.755 50.250 ↓ 1.8 5,952 5

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

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

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

34. 18.397 22.245 ↑ 1.0 10,152 5

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

  • Sort Key: t_2.home, ((t_2.date)::date) DESC
  • Sort Method: quicksort Memory: 940kB
35. 3.848 3.848 ↑ 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.848 rows=10,152 loops=1)

36. 0.475 29.012 ↓ 7.4 2,049 1

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

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

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

38. 4.323 28.300 ↓ 7.4 2,049 1

GroupAggregate (cost=962.87..1,448.52 rows=278 width=74) (actual time=17.451..28.300 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.707 23.977 ↓ 1.8 5,964 1

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

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

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

41. 14.038 18.341 ↑ 1.0 10,152 1

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

  • Sort Key: t_3.away, ((t_3.date)::date) DESC
  • Sort Method: quicksort Memory: 1,174kB
42. 4.303 4.303 ↑ 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.303 rows=10,152 loops=1)

Planning time : 1.283 ms
Execution time : 3,918.273 ms