explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Npog

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 2,112.928 ↓ 2.0 2 1

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

2.          

CTE acc_statuses

3. 37.808 324.599 ↑ 2.4 21,396 1

HashAggregate (cost=2,126,907.81..2,127,430.86 rows=52,305 width=16) (actual time=310.444..324.599 rows=21,396 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 286.791 ↑ 3.8 64,482 1

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

5. 162.710 162.710 ↑ 3.8 64,482 1

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

  • Filter: ((status_id = 1) AND ((bet_accept_moment)::date > '2019-09-25'::date))
  • Rows Removed by Filter: 673,624
6. 64.482 128.964 ↓ 0.0 0 64,482

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

7. 64.482 64.482 ↓ 0.0 0 64,482

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.001..0.001 rows=0 loops=64,482)

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

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

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

GroupAggregate (cost=33,462.86..33,462.96 rows=1 width=123) (actual time=2,093.038..2,112.902 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,157
10. 88.246 2,073.913 ↓ 71,266.0 71,266 1

Sort (cost=33,462.86..33,462.87 rows=1 width=155) (actual time=2,068.440..2,073.913 rows=71,266 loops=1)

  • Sort Key: m.event_id, ("left"((m.live_prematch)::text, 1)), e.name_ru, s.name
  • Sort Method: quicksort Memory: 21,672kB
11. 145.023 1,985.667 ↓ 71,266.0 71,266 1

Nested Loop (cost=32,196.59..33,462.85 rows=1 width=155) (actual time=727.781..1,985.667 rows=71,266 loops=1)

  • Join Filter: (COALESCE(ac.ext_status_id, 0) = es.id)
  • Rows Removed by Join Filter: 388,138
12. 138.136 1,769.378 ↓ 71,266.0 71,266 1

Nested Loop (cost=32,196.59..33,458.81 rows=1 width=123) (actual time=727.757..1,769.378 rows=71,266 loops=1)

  • Join Filter: (e.sport_id = s.id)
  • Rows Removed by Join Filter: 1,196,803
13. 9.709 1,488.710 ↓ 71,266.0 71,266 1

Nested Loop (cost=32,196.59..33,454.11 rows=1 width=117) (actual time=727.732..1,488.710 rows=71,266 loops=1)

14. 47.015 1,265.203 ↓ 71,266.0 71,266 1

Nested Loop Left Join (cost=32,196.16..33,445.66 rows=1 width=76) (actual time=727.698..1,265.203 rows=71,266 loops=1)

15. 42.848 790.592 ↓ 71,266.0 71,266 1

Hash Join (cost=32,195.74..33,438.00 rows=1 width=94) (actual time=727.610..790.592 rows=71,266 loops=1)

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

CTE Scan on acc_statuses ast (cost=0.00..1,046.10 rows=52,305 width=16) (actual time=310.451..330.649 rows=21,396 loops=1)

17. 43.940 417.095 ↓ 986.4 76,939 1

Hash (cost=32,194.77..32,194.77 rows=78 width=86) (actual time=417.095..417.095 rows=76,939 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 10,179kB
18. 3.507 373.155 ↓ 986.4 76,939 1

Nested Loop (cost=0.86..32,194.77 rows=78 width=86) (actual time=29.800..373.155 rows=76,939 loops=1)

19. 61.892 61.892 ↓ 986.4 76,939 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=29.614..61.892 rows=76,939 loops=1)

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

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

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

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

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

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

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

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

24. 71.266 71.266 ↑ 4.0 6 71,266

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