explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3xDO

Settings
# exclusive inclusive rows x rows loops node
1. 0.129 2,375.906 ↑ 1.0 50 1

Limit (cost=1,947,440.91..1,947,441.04 rows=50 width=123) (actual time=2,375.774..2,375.906 rows=50 loops=1)

2.          

CTE acc_statuses

3. 25.196 282.831 ↑ 3.5 14,786 1

HashAggregate (cost=1,869,337.06..1,869,858.20 rows=52,114 width=16) (actual time=279.432..282.831 rows=14,786 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 257.635 ↑ 3.2 66,319 1

Nested Loop Left Join (cost=0.41..1,867,727.78 rows=214,570 width=16) (actual time=105.655..257.635 rows=66,319 loops=1)

5. 127.932 127.932 ↑ 3.2 66,319 1

Seq Scan on bot_bets_match m_1 (cost=0.00..54,074.86 rows=214,570 width=4) (actual time=105.597..127.932 rows=66,319 loops=1)

  • Filter: ((status_id = 1) AND ((bet_accept_moment)::date > '2019-09-18'::date))
  • Rows Removed by Filter: 599,074
6. 0.000 132.638 ↓ 0.0 0 66,319

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

7. 132.638 132.638 ↓ 0.0 0 66,319

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.002 rows=0 loops=66,319)

  • Index Cond: (account_id = m_1.account_id)
8. 0.063 2,375.777 ↑ 5.9 50 1

Sort (cost=77,582.71..77,583.45 rows=294 width=123) (actual time=2,375.772..2,375.777 rows=50 loops=1)

  • Sort Key: (min(m.bot_bet_accept_moment)) DESC
  • Sort Method: quicksort Memory: 37kB
9. 46.671 2,375.714 ↑ 4.1 71 1

GroupAggregate (cost=77,496.59..77,572.95 rows=294 width=123) (actual time=2,316.113..2,375.714 rows=71 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 >= '$9.00'::money)) > 0)
  • Rows Removed by Filter: 3,411
10. 83.074 2,329.043 ↓ 82.7 72,846 1

Sort (cost=77,496.59..77,498.79 rows=881 width=155) (actual time=2,315.869..2,329.043 rows=72,846 loops=1)

  • Sort Key: m.event_id, ("left"((m.live_prematch)::text, 1)), e.name_ru, s.name
  • Sort Method: quicksort Memory: 22,213kB
11. 20.764 2,245.969 ↓ 82.7 72,846 1

Nested Loop (cost=76,480.61..77,453.50 rows=881 width=155) (actual time=1,595.905..2,245.969 rows=72,846 loops=1)

12. 51.264 2,152.359 ↓ 82.7 72,846 1

Nested Loop Left Join (cost=76,480.47..77,308.41 rows=881 width=121) (actual time=1,595.889..2,152.359 rows=72,846 loops=1)

13. 42.014 1,664.019 ↓ 82.7 72,846 1

Merge Join (cost=76,480.18..76,754.27 rows=881 width=139) (actual time=1,595.856..1,664.019 rows=72,846 loops=1)

  • Merge Cond: (m.account_id = ast.account_id)
14. 53.139 1,323.410 ↓ 82.4 77,652 1

Sort (cost=71,354.93..71,357.29 rows=942 width=131) (actual time=1,304.589..1,323.410 rows=77,652 loops=1)

  • Sort Key: m.account_id
  • Sort Method: quicksort Memory: 23,652kB
15. 17.322 1,270.271 ↓ 82.4 77,653 1

Gather (cost=1,005.33..71,308.40 rows=942 width=131) (actual time=672.412..1,270.271 rows=77,653 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 4.918 1,252.949 ↓ 66.0 25,884 3 / 3

Nested Loop (cost=5.33..70,214.20 rows=392 width=131) (actual time=665.710..1,252.949 rows=25,884 loops=3)

17. 8.052 1,170.378 ↓ 66.0 25,884 3 / 3

Hash Join (cost=4.90..67,125.48 rows=392 width=90) (actual time=665.659..1,170.378 rows=25,884 loops=3)

  • Hash Cond: (COALESCE(ac.ext_status_id, 0) = es.id)
18. 24.720 1,162.286 ↓ 7.9 25,884 3 / 3

Nested Loop (cost=0.86..67,112.68 rows=3,271 width=86) (actual time=665.469..1,162.286 rows=25,884 loops=3)

19. 1,008.144 1,008.144 ↓ 7.9 25,884 3 / 3

Parallel Index Scan using bot_bets_status_id_bet_accept_moment_idx on bot_bets_match m (cost=0.42..42,999.13 rows=3,271 width=78) (actual time=665.392..1,008.144 rows=25,884 loops=3)

  • Index Cond: (bet_accept_moment > '2019-09-18'::date)
20. 129.422 129.422 ↑ 1.0 1 77,653 / 3

Index Scan using account_pkey on account ac (cost=0.43..7.37 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=77,653)

  • Index Cond: (id = m.account_id)
21. 0.008 0.040 ↑ 1.4 17 3 / 3

Hash (cost=3.74..3.74 rows=24 width=8) (actual time=0.039..0.040 rows=17 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.032 0.032 ↑ 1.4 17 3 / 3

Seq Scan on ext_status es (cost=0.00..3.74 rows=24 width=8) (actual time=0.030..0.032 rows=17 loops=3)

23. 77.653 77.653 ↑ 1.0 1 77,653 / 3

Index Scan using event_pkey on event e (cost=0.43..7.88 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=77,653)

  • Index Cond: (id = m.event_id)
24. 11.572 298.595 ↓ 1.4 72,846 1

Sort (cost=5,125.25..5,255.54 rows=52,114 width=16) (actual time=291.258..298.595 rows=72,846 loops=1)

  • Sort Key: ast.account_id
  • Sort Method: quicksort Memory: 1,540kB
25. 287.023 287.023 ↑ 3.5 14,786 1

CTE Scan on acc_statuses ast (cost=0.00..1,042.28 rows=52,114 width=16) (actual time=279.436..287.023 rows=14,786 loops=1)

26. 437.076 437.076 ↑ 1.0 1 72,846

Index Scan using bot_bets_match_count_good_selection_id_line_item_id_idx on bot_bets_match_count_good cg (cost=0.29..0.63 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=72,846)

  • Index Cond: ((line_item_id = m.line_item_id) AND (selection_id = m.selection_id))
27. 72.846 72.846 ↑ 1.0 1 72,846

Index Scan using sport_new_pkey on sport s (cost=0.14..0.16 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=72,846)

  • Index Cond: (id = e.sport_id)