explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N9Af

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 2,191.110 ↑ 3.0 1 1

Limit (cost=2,161,416.42..2,161,416.43 rows=3 width=123) (actual time=2,191.109..2,191.110 rows=1 loops=1)

2.          

CTE acc_statuses

3. 27.729 321.229 ↑ 2.4 21,374 1

HashAggregate (cost=2,126,751.03..2,127,274.08 rows=52,305 width=16) (actual time=316.477..321.229 rows=21,374 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. 3.295 293.500 ↑ 3.8 64,443 1

Nested Loop Left Join (cost=0.41..2,124,920.16 rows=244,117 width=16) (actual time=140.205..293.500 rows=64,443 loops=1)

5. 161.319 161.319 ↑ 3.8 64,443 1

Seq Scan on bot_bets_match m_1 (cost=0.00..61,521.22 rows=244,117 width=4) (actual time=140.166..161.319 rows=64,443 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.886 ↓ 0.0 0 64,443

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

7. 128.886 128.886 ↓ 0.0 0 64,443

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,443)

  • Index Cond: (account_id = m_1.account_id)
8. 0.007 2,191.107 ↑ 3.0 1 1

Sort (cost=34,142.34..34,142.34 rows=3 width=123) (actual time=2,191.107..2,191.107 rows=1 loops=1)

  • Sort Key: (min(m.bot_bet_accept_moment)) DESC
  • Sort Method: quicksort Memory: 25kB
9. 35.363 2,191.100 ↑ 3.0 1 1

GroupAggregate (cost=34,141.53..34,142.31 rows=3 width=123) (actual time=2,173.258..2,191.100 rows=1 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,155
10. 47.883 2,155.737 ↓ 7,913.7 71,223 1

Sort (cost=34,141.53..34,141.56 rows=9 width=155) (actual time=2,150.699..2,155.737 rows=71,223 loops=1)

  • Sort Key: m.event_id, ("left"((m.live_prematch)::text, 1)), e.name_ru, s.name
  • Sort Method: quicksort Memory: 21,660kB
11. 449.688 2,107.854 ↓ 7,913.7 71,223 1

Nested Loop (cost=1,701.62..34,141.39 rows=9 width=155) (actual time=1,479.795..2,107.854 rows=71,223 loops=1)

  • Join Filter: (e.sport_id = s.id)
  • Rows Removed by Join Filter: 3,774,819
12. 0.096 0.096 ↓ 1.0 54 1

Seq Scan on sport s (cost=0.00..4.03 rows=53 width=14) (actual time=0.008..0.096 rows=54 loops=1)

13. 223.657 1,658.070 ↓ 7,913.7 71,223 54

Materialize (cost=1,701.62..34,130.16 rows=9 width=121) (actual time=6.827..30.705 rows=71,223 loops=54)

14. 20.329 1,434.413 ↓ 7,913.7 71,223 1

Nested Loop (cost=1,701.62..34,130.12 rows=9 width=121) (actual time=368.637..1,434.413 rows=71,223 loops=1)

15. 15.682 1,271.638 ↓ 7,913.7 71,223 1

Nested Loop Left Join (cost=1,701.18..34,054.07 rows=9 width=80) (actual time=368.620..1,271.638 rows=71,223 loops=1)

16. 35.735 828.618 ↓ 7,913.7 71,223 1

Hash Join (cost=1,700.77..33,985.07 rows=9 width=98) (actual time=368.592..828.618 rows=71,223 loops=1)

  • Hash Cond: (m.account_id = ast.account_id)
17. 77.953 460.596 ↓ 8,544.2 76,898 1

Nested Loop (cost=0.86..32,226.22 rows=9 width=90) (actual time=36.152..460.596 rows=76,898 loops=1)

  • Join Filter: (COALESCE(ac.ext_status_id, 0) = es.id)
  • Rows Removed by Join Filter: 436,265
18. 3.547 382.643 ↓ 985.9 76,898 1

Nested Loop (cost=0.86..32,194.34 rows=78 width=86) (actual time=36.133..382.643 rows=76,898 loops=1)

19. 71.504 71.504 ↓ 985.9 76,898 1

Index Scan using bot_bets_status_id_bet_accept_moment_idx on bot_bets_match m (cost=0.42..31,535.24 rows=78 width=78) (actual time=36.100..71.504 rows=76,898 loops=1)

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

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

  • Index Cond: (id = m.account_id)
21. 0.000 0.000 ↑ 3.4 7 76,898

Materialize (cost=0.00..3.86 rows=24 width=8) (actual time=0.000..0.000 rows=7 loops=76,898)

22. 0.016 0.016 ↑ 1.4 17 1

Seq Scan on ext_status es (cost=0.00..3.74 rows=24 width=8) (actual time=0.012..0.016 rows=17 loops=1)

23. 3.712 332.287 ↑ 2.4 21,374 1

Hash (cost=1,046.10..1,046.10 rows=52,305 width=16) (actual time=332.287..332.287 rows=21,374 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,598kB
24. 328.575 328.575 ↑ 2.4 21,374 1

CTE Scan on acc_statuses ast (cost=0.00..1,046.10 rows=52,305 width=16) (actual time=316.480..328.575 rows=21,374 loops=1)

25. 427.338 427.338 ↑ 1.0 1 71,223

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,223)

  • Index Cond: ((line_item_id = m.line_item_id) AND (selection_id = m.selection_id))
26. 142.446 142.446 ↑ 1.0 1 71,223

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,223)

  • Index Cond: (id = m.event_id)