explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iqLW

Settings
# exclusive inclusive rows x rows loops node
1. 0.571 2,644.146 ↓ 1.4 42 1

Nested Loop Left Join (cost=0.86..534,934.49 rows=29 width=1,680) (actual time=2,365.893..2,644.146 rows=42 loops=1)

  • Join Filter: (events.id = market.event_id)
  • Rows Removed by Join Filter: 3570
  • Functions: 107
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 24.237 ms, Inlining 27.196 ms, Optimization 1470.492 ms, Emission 820.314 ms, Total 2342.239 ms
2. 0.193 2,641.916 ↓ 2.6 21 1

Nested Loop Left Join (cost=0.15..534,917.45 rows=8 width=1,464) (actual time=2,365.812..2,641.916 rows=21 loops=1)

  • Join Filter: (competition_1.region_id = region.id)
  • Rows Removed by Join Filter: 744
3. 0.701 2,641.513 ↓ 2.6 21 1

Nested Loop Left Join (cost=0.15..534,901.21 rows=8 width=897) (actual time=2,365.798..2,641.513 rows=21 loops=1)

  • Join Filter: (events.league_id = competition_1.id)
  • Rows Removed by Join Filter: 5344
4. 2.837 2,639.930 ↓ 2.6 21 1

Nested Loop (cost=0.15..534,845.58 rows=8 width=857) (actual time=2,365.594..2,639.930 rows=21 loops=1)

  • Join Filter: (competition.id = events.league_id)
  • Rows Removed by Join Filter: 18207
5. 2,321.337 2,321.337 ↑ 1.0 42 1

Index Scan using competition_id on competition (cost=0.15..16.13 rows=42 width=40) (actual time=2,321.166..2,321.337 rows=42 loops=1)

  • Filter: (sport_id = 91)
  • Rows Removed by Filter: 325
6. 3.919 315.756 ↓ 5.9 434 42

Materialize (cost=0.00..534,783.64 rows=73 width=817) (actual time=0.013..7.518 rows=434 loops=42)

7. 0.291 311.837 ↓ 5.9 434 1

Subquery Scan on events (cost=0.00..534,783.28 rows=73 width=817) (actual time=0.534..311.837 rows=434 loops=1)

8. 5.576 311.546 ↓ 5.9 434 1

Seq Scan on event (cost=0.00..534,782.55 rows=73 width=817) (actual time=0.531..311.546 rows=434 loops=1)

  • Filter: ((deleted IS NOT TRUE) AND (CASE WHEN (bet365_live_id IS NOT NULL) THEN true ELSE false END IS NOT TRUE) AND (start_ts >= '1581544800'::bigint) AND (start_ts <= '1581631199'::bigint) AND ((SubPlan 7) > 0))
  • Rows Removed by Filter: 1144
9.          

SubPlan (for Seq Scan)

10. 2.170 10.850 ↑ 1.0 1 434

Aggregate (cost=310.11..310.12 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=434)

11. 8.680 8.680 ↓ 0.0 0 434

Index Scan using market_event_id on market market_1 (cost=0.29..310.10 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=434)

  • Index Cond: (event_id = event.id)
  • Filter: ((is_live IS TRUE) AND (CASE WHEN ((SubPlan 2) > 0) THEN 1 ELSE 2 END = 1))
  • Rows Removed by Filter: 33
12.          

SubPlan (for Index Scan)

13. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=6.96..6.97 rows=1 width=8) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using outcome_market_id on outcome outcome_2 (cost=0.42..6.94 rows=6 width=8) (never executed)

  • Index Cond: (market_id = market_1.id)
  • Filter: (deleted IS NOT TRUE)
15. 6.944 138.012 ↑ 1.0 1 434

Aggregate (cost=310.11..310.12 rows=1 width=8) (actual time=0.318..0.318 rows=1 loops=434)

16. 17.812 131.068 ↓ 32.0 32 434

Index Scan using market_event_id on market market_2 (cost=0.29..310.10 rows=1 width=8) (actual time=0.015..0.302 rows=32 loops=434)

  • Index Cond: (event_id = event.id)
  • Filter: ((is_live IS NOT TRUE) AND (CASE WHEN ((SubPlan 4) > 0) THEN 1 ELSE 2 END = 1))
  • Rows Removed by Filter: 0
17.          

SubPlan (for Index Scan)

18. 28.314 113.256 ↑ 1.0 1 14,157

Aggregate (cost=6.96..6.97 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=14,157)

19. 84.942 84.942 ↑ 2.0 3 14,157

Index Scan using outcome_market_id on outcome outcome_3 (cost=0.42..6.94 rows=6 width=8) (actual time=0.004..0.006 rows=3 loops=14,157)

  • Index Cond: (market_id = market_2.id)
  • Filter: (deleted IS NOT TRUE)
  • Rows Removed by Filter: 0
20. 7.812 157.108 ↑ 1.0 1 434

Aggregate (cost=310.11..310.12 rows=1 width=8) (actual time=0.362..0.362 rows=1 loops=434)

21. 21.883 149.296 ↓ 32.0 32 434

Index Scan using market_event_id on market market_3 (cost=0.29..310.10 rows=1 width=8) (actual time=0.019..0.344 rows=32 loops=434)

  • Index Cond: (event_id = event.id)
  • Filter: ((is_live IS NOT TRUE) AND (CASE WHEN ((SubPlan 6) > 0) THEN 1 ELSE 2 END = 1))
  • Rows Removed by Filter: 0
22.          

SubPlan (for Index Scan)

23. 28.314 127.413 ↑ 1.0 1 14,157

Aggregate (cost=6.96..6.97 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=14,157)

24. 99.099 99.099 ↑ 2.0 3 14,157

Index Scan using outcome_market_id on outcome outcome_4 (cost=0.42..6.94 rows=6 width=8) (actual time=0.005..0.007 rows=3 loops=14,157)

  • Index Cond: (market_id = market_3.id)
  • Filter: (deleted IS NOT TRUE)
  • Rows Removed by Filter: 0
25. 0.766 0.882 ↑ 1.4 255 21

Materialize (cost=0.00..12.50 rows=367 width=40) (actual time=0.001..0.042 rows=255 loops=21)

26. 0.116 0.116 ↑ 1.0 361 1

Seq Scan on competition competition_1 (cost=0.00..10.67 rows=367 width=40) (actual time=0.013..0.116 rows=361 loops=1)

27. 0.175 0.210 ↑ 3.2 36 21

Materialize (cost=0.00..2.73 rows=115 width=567) (actual time=0.001..0.010 rows=36 loops=21)

28. 0.035 0.035 ↓ 1.0 120 1

Seq Scan on region (cost=0.00..2.15 rows=115 width=567) (actual time=0.006..0.035 rows=120 loops=1)

29. 0.616 1.659 ↓ 43.0 172 21

Materialize (cost=0.71..16.50 rows=4 width=214) (actual time=0.003..0.079 rows=172 loops=21)

30. 0.053 1.043 ↓ 43.0 172 1

Nested Loop Left Join (cost=0.71..16.48 rows=4 width=214) (actual time=0.048..1.043 rows=172 loops=1)

31. 0.130 0.646 ↓ 86.0 86 1

Index Scan using market_type_id_idx on market (cost=0.29..9.48 rows=1 width=93) (actual time=0.035..0.646 rows=86 loops=1)

  • Index Cond: (type_id = '910000'::bigint)
32.          

SubPlan (for Index Scan)

33. 0.086 0.516 ↑ 1.0 1 86

Aggregate (cost=6.96..6.97 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=86)

34. 0.430 0.430 ↑ 3.0 2 86

Index Scan using outcome_market_id on outcome outcome_1 (cost=0.42..6.94 rows=6 width=8) (actual time=0.004..0.005 rows=2 loops=86)

  • Index Cond: (market_id = market.id)
  • Filter: (deleted IS NOT TRUE)
35. 0.344 0.344 ↑ 3.0 2 86

Index Scan using outcome_market_id on outcome (cost=0.42..6.94 rows=6 width=121) (actual time=0.003..0.004 rows=2 loops=86)

  • Index Cond: (market_id = market.id)
Planning time : 5.638 ms
Execution time : 2,669.465 ms