explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c8dF

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,547.137 ↑ 1.5 2 1

Limit (cost=2,161,562.40..2,161,562.40 rows=3 width=113) (actual time=1,547.136..1,547.137 rows=2 loops=1)

2.          

CTE acc_statuses

3. 27.440 311.700 ↑ 2.4 21,396 1

HashAggregate (cost=2,126,907.81..2,127,430.86 rows=52,305 width=16) (actual time=307.354..311.700 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 284.260 ↑ 3.8 64,487 1

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

5. 157.515 157.515 ↑ 3.8 64,487 1

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

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

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

7. 64.487 64.487 ↓ 0.0 0 64,487

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

  • Index Cond: (account_id = m_1.account_id)
8. 0.008 1,547.135 ↑ 1.5 2 1

Sort (cost=34,131.53..34,131.54 rows=3 width=113) (actual time=1,547.135..1,547.135 rows=2 loops=1)

  • Sort Key: (min(m.bot_bet_accept_moment)) DESC
  • Sort Method: quicksort Memory: 25kB
9. 34.850 1,547.127 ↑ 1.5 2 1

GroupAggregate (cost=34,130.75..34,131.51 rows=3 width=113) (actual time=1,528.740..1,547.127 rows=2 loops=1)

  • Group Key: m.event_id, ("left"((m.live_prematch)::text, 1)), e.name_ru
  • 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,159
10. 60.370 1,512.277 ↓ 7,919.0 71,271 1

Sort (cost=34,130.75..34,130.78 rows=9 width=145) (actual time=1,506.589..1,512.277 rows=71,271 loops=1)

  • Sort Key: m.event_id, ("left"((m.live_prematch)::text, 1)), e.name_ru
  • Sort Method: quicksort Memory: 20,341kB
11. 34.235 1,451.907 ↓ 7,919.0 71,271 1

Nested Loop (cost=1,701.62..34,130.61 rows=9 width=145) (actual time=356.695..1,451.907 rows=71,271 loops=1)

12. 14.334 1,275.130 ↓ 7,919.0 71,271 1

Nested Loop Left Join (cost=1,701.18..34,054.49 rows=9 width=80) (actual time=356.673..1,275.130 rows=71,271 loops=1)

13. 33.909 833.170 ↓ 7,919.0 71,271 1

Hash Join (cost=1,700.77..33,985.50 rows=9 width=98) (actual time=356.633..833.170 rows=71,271 loops=1)

  • Hash Cond: (m.account_id = ast.account_id)
14. 76.116 476.838 ↓ 8,549.3 76,944 1

Nested Loop (cost=0.86..32,226.65 rows=9 width=90) (actual time=34.049..476.838 rows=76,944 loops=1)

  • Join Filter: (COALESCE(ac.ext_status_id, 0) = es.id)
  • Rows Removed by Join Filter: 436,667
15. 21.229 400.722 ↓ 986.5 76,944 1

Nested Loop (cost=0.86..32,194.77 rows=78 width=86) (actual time=34.026..400.722 rows=76,944 loops=1)

16. 71.717 71.717 ↓ 986.5 76,944 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=33.993..71.717 rows=76,944 loops=1)

  • Index Cond: (bet_accept_moment > '2019-09-25'::date)
17. 307.776 307.776 ↑ 1.0 1 76,944

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

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

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

19. 0.020 0.020 ↑ 1.4 17 1

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

20. 3.630 322.423 ↑ 2.4 21,396 1

Hash (cost=1,046.10..1,046.10 rows=52,305 width=16) (actual time=322.422..322.423 rows=21,396 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,599kB
21. 318.793 318.793 ↑ 2.4 21,396 1

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

22. 427.626 427.626 ↑ 1.0 1 71,271

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

  • Index Cond: ((line_item_id = m.line_item_id) AND (selection_id = m.selection_id))
23. 142.542 142.542 ↑ 1.0 1 71,271

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

  • Index Cond: (id = m.event_id)