explain.depesz.com

PostgreSQL's explain analyze made readable

Result: obOR

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 2,098.527 ↓ 2.0 2 1

Limit (cost=2,160,893.83..2,160,893.84 rows=1 width=123) (actual time=2,098.526..2,098.527 rows=2 loops=1)

2.          

CTE acc_statuses

3. 33.861 321.008 ↑ 2.4 21,386 1

HashAggregate (cost=2,126,907.81..2,127,430.86 rows=52,305 width=16) (actual time=311.409..321.008 rows=21,386 loops=1)

  • Group Key: m_1.account_id, COALESCE(w.status_id, '-1'::integer), COALESCE(w.bet_accept_moment, '1900-01-01 00:00:00'::timestamp without time zone)
4. 0.000 287.147 ↑ 3.8 64,463 1

Nested Loop Left Join (cost=0.41..2,125,076.80 rows=244,135 width=16) (actual time=137.988..287.147 rows=64,463 loops=1)

5. 158.278 158.278 ↑ 3.8 64,463 1

Seq Scan on bot_bets_match m_1 (cost=0.00..61,525.71 rows=244,135 width=4) (actual time=137.940..158.278 rows=64,463 loops=1)

  • Filter: ((status_id = 1) AND ((bet_accept_moment)::date > '2019-09-25'::date))
  • Rows Removed by Filter: 673,622
6. 0.000 128.926 ↓ 0.0 0 64,463

Limit (cost=0.41..8.43 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=64,463)

7. 128.926 128.926 ↓ 0.0 0 64,463

Index Scan Backward using bot_bets_account_create_moment_idx on bot_bets_match w (cost=0.41..8.43 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=64,463)

  • Index Cond: (account_id = m_1.account_id)
8. 0.027 2,098.510 ↓ 2.0 2 1

Sort (cost=33,462.97..33,462.98 rows=1 width=123) (actual time=2,098.510..2,098.510 rows=2 loops=1)

  • Sort Key: (min(m.bot_bet_accept_moment)) DESC
  • Sort Method: quicksort Memory: 25kB
9. 41.115 2,098.483 ↓ 2.0 2 1

GroupAggregate (cost=33,462.86..33,462.96 rows=1 width=123) (actual time=2,073.644..2,098.483 rows=2 loops=1)

  • Group Key: m.event_id, ("left"((m.live_prematch)::text, 1)), e.name_ru, s.name
  • Filter: (count(DISTINCT CASE WHEN (m.status_id > 1) THEN NULL::bigint WHEN (ac.ext_status_id = 32) THEN NULL::bigint WHEN ((ast.last_status_id = 3) AND (es.score = 'good'::status_type_type) AND (((m.ratio_w < 3) AND (m.ratio_c_bet_item < 3)) OR (COALESCE(cg.count_good_accounts, 0) > 3))) THEN NULL::bigint WHEN ((ast.last_status_id = '-1'::integer) AND (es.score = 'good'::status_type_type) AND (((m.ratio_w < 1) AND (m.ratio_c_bet_item < 1)) OR (COALESCE(cg.count_good_accounts, 0) > 3))) THEN NULL::bigint WHEN (m.bet_accept_moment < ast.bet_accept_moment) THEN NULL::bigint ELSE m.bet_item_id END) FILTER (WHERE (m.bet_item_stake >= '$5.00'::money)) > 0)
  • Rows Removed by Filter: 3,156
10. 97.363 2,057.368 ↓ 71,247.0 71,247 1

Sort (cost=33,462.86..33,462.87 rows=1 width=155) (actual time=2,030.030..2,057.368 rows=71,247 loops=1)

  • Sort Key: m.event_id, ("left"((m.live_prematch)::text, 1)), e.name_ru, s.name
  • Sort Method: quicksort Memory: 21,667kB
11. 142.470 1,960.005 ↓ 71,247.0 71,247 1

Nested Loop (cost=32,196.59..33,462.85 rows=1 width=155) (actual time=694.557..1,960.005 rows=71,247 loops=1)

  • Join Filter: (COALESCE(ac.ext_status_id, 0) = es.id)
  • Rows Removed by Join Filter: 388,005
12. 145.319 1,746.288 ↓ 71,247.0 71,247 1

Nested Loop (cost=32,196.59..33,458.81 rows=1 width=123) (actual time=694.534..1,746.288 rows=71,247 loops=1)

  • Join Filter: (e.sport_id = s.id)
  • Rows Removed by Join Filter: 1,196,556
13. 73.987 1,458.475 ↓ 71,247.0 71,247 1

Nested Loop (cost=32,196.59..33,454.11 rows=1 width=117) (actual time=694.504..1,458.475 rows=71,247 loops=1)

14. 59.727 1,241.994 ↓ 71,247.0 71,247 1

Nested Loop Left Join (cost=32,196.16..33,445.66 rows=1 width=76) (actual time=694.475..1,241.994 rows=71,247 loops=1)

15. 44.554 754.785 ↓ 71,247.0 71,247 1

Hash Join (cost=32,195.74..33,438.00 rows=1 width=94) (actual time=694.444..754.785 rows=71,247 loops=1)

  • Hash Cond: (ast.account_id = ac.id)
16. 327.227 327.227 ↑ 2.4 21,386 1

CTE Scan on acc_statuses ast (cost=0.00..1,046.10 rows=52,305 width=16) (actual time=311.411..327.227 rows=21,386 loops=1)

17. 26.005 383.004 ↓ 986.1 76,918 1

Hash (cost=32,194.77..32,194.77 rows=78 width=86) (actual time=383.003..383.004 rows=76,918 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 10,177kB
18. 56.093 356.999 ↓ 986.1 76,918 1

Nested Loop (cost=0.86..32,194.77 rows=78 width=86) (actual time=37.653..356.999 rows=76,918 loops=1)

19. 70.152 70.152 ↓ 986.1 76,918 1

Index Scan using bot_bets_status_id_bet_accept_moment_idx on bot_bets_match m (cost=0.42..31,535.67 rows=78 width=78) (actual time=37.618..70.152 rows=76,918 loops=1)

  • Index Cond: (bet_accept_moment > '2019-09-25'::date)
20. 230.754 230.754 ↑ 1.0 1 76,918

Index Scan using account_pkey on account ac (cost=0.43..8.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=76,918)

  • Index Cond: (id = m.account_id)
21. 427.482 427.482 ↑ 1.0 1 71,247

Index Scan using bot_bets_match_count_good_selection_id_line_item_id_idx on bot_bets_match_count_good cg (cost=0.41..7.67 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=71,247)

  • Index Cond: ((line_item_id = m.line_item_id) AND (selection_id = m.selection_id))
22. 142.494 142.494 ↑ 1.0 1 71,247

Index Scan using event_pkey on event e (cost=0.43..8.45 rows=1 width=45) (actual time=0.002..0.002 rows=1 loops=71,247)

  • Index Cond: (id = m.event_id)
23. 142.494 142.494 ↑ 2.9 18 71,247

Seq Scan on sport s (cost=0.00..4.03 rows=53 width=14) (actual time=0.001..0.002 rows=18 loops=71,247)

24. 71.247 71.247 ↑ 4.0 6 71,247

Seq Scan on ext_status es (cost=0.00..3.74 rows=24 width=8) (actual time=0.001..0.001 rows=6 loops=71,247)