explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2AS

Settings
# exclusive inclusive rows x rows loops node
1. 689.467 10,507.124 ↓ 1.2 348,159 1

HashAggregate (cost=2,619,184.44..2,627,220.19 rows=292,209 width=158) (actual time=10,193.845..10,507.124 rows=348,159 loops=1)

  • Group Key: accs.id, accs.balance, accs.account_type, accs.account_reg_date, accs.currency_id, pu.country_id
  • Filter: (sum(COALESCE(stat.commission, '0'::numeric)) >= '0'::numeric)
2. 49.266 9,817.657 ↓ 1.2 348,159 1

Hash Left Join (cost=2,537,102.26..2,611,148.69 rows=292,209 width=158) (actual time=8,010.924..9,817.657 rows=348,159 loops=1)

  • Hash Cond: (accs.id = stat.server_account_number)
3. 1,633.888 9,587.197 ↓ 1.2 348,159 1

Hash Left Join (cost=2,405,571.61..2,477,967.06 rows=292,209 width=30) (actual time=7,829.713..9,587.197 rows=348,159 loops=1)

  • Hash Cond: (accs.user_id = pu.id)
4. 25.948 3,568.287 ↓ 1.2 348,159 1

Subquery Scan on accs (cost=1,788,596.62..1,794,440.80 rows=292,209 width=30) (actual time=3,436.890..3,568.287 rows=348,159 loops=1)

5. 298.163 3,542.339 ↓ 1.2 348,159 1

HashAggregate (cost=1,788,596.62..1,791,518.71 rows=292,209 width=34) (actual time=3,436.889..3,542.339 rows=348,159 loops=1)

  • Group Key: a_mt4.server_account_number, a_mt4.currency_id, a.user_id, a.created_dt, a_t.name, a.balance, a.platform_id
6. 71.016 3,244.176 ↓ 1.2 348,287 1

Hash Join (cost=306,647.99..1,783,482.96 rows=292,209 width=34) (actual time=560.929..3,244.176 rows=348,287 loops=1)

  • Hash Cond: (a_mt4.type_id = a_t.id)
7. 131.514 3,173.148 ↓ 1.2 348,287 1

Nested Loop (cost=306,646.45..1,779,463.55 rows=292,209 width=31) (actual time=560.909..3,173.148 rows=348,287 loops=1)

8. 83.489 1,730.559 ↓ 1.1 437,025 1

Hash Join (cost=306,645.89..1,474,790.02 rows=387,020 width=23) (actual time=560.891..1,730.559 rows=437,025 loops=1)

  • Hash Cond: (a.platform_id = ap.id)
9. 80.130 1,647.062 ↓ 1.1 445,244 1

Nested Loop (cost=306,644.52..1,469,363.46 rows=414,664 width=23) (actual time=560.877..1,647.062 rows=445,244 loops=1)

10. 93.785 709.520 ↓ 1.9 214,353 1

Hash Join (cost=306,643.96..322,275.65 rows=112,276 width=16) (actual time=560.850..709.520 rows=214,353 loops=1)

  • Hash Cond: (ah.client_id = u.id)
11. 56.639 70.367 ↑ 1.0 214,974 1

Bitmap Heap Scan on partner_attachment_history ah (cost=4,460.71..18,886.63 rows=224,553 width=12) (actual time=14.976..70.367 rows=214,974 loops=1)

  • Recheck Cond: (partner_id = 359091)
  • Heap Blocks: exact=9793
12. 13.728 13.728 ↑ 1.0 214,974 1

Bitmap Index Scan on partner_attachment_history_idx_partner_id (cost=0.00..4,404.57 rows=224,553 width=0) (actual time=13.728..13.728 rows=214,974 loops=1)

  • Index Cond: (partner_id = 359091)
13. 24.411 545.368 ↑ 1.4 214,316 1

Hash (cost=298,549.44..298,549.44 rows=290,705 width=4) (actual time=545.368..545.368 rows=214,316 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 11631kB
14. 140.596 520.957 ↑ 1.4 214,316 1

HashAggregate (cost=292,735.34..295,642.39 rows=290,705 width=4) (actual time=485.783..520.957 rows=214,316 loops=1)

  • Group Key: u.id
15. 18.164 380.361 ↓ 1.5 428,630 1

Append (cost=6,709.72..292,008.57 rows=290,705 width=4) (actual time=41.658..380.361 rows=428,630 loops=1)

16. 163.308 188.260 ↑ 1.1 214,314 1

Bitmap Heap Scan on "user" u (cost=6,709.72..268,436.89 rows=235,005 width=4) (actual time=41.657..188.260 rows=214,314 loops=1)

  • Recheck Cond: (partner_id = 359091)
  • Heap Blocks: exact=101878
17. 24.952 24.952 ↑ 1.1 214,618 1

Bitmap Index Scan on user_partner_id (cost=0.00..6,650.97 rows=235,005 width=0) (actual time=24.952..24.952 rows=214,618 loops=1)

  • Index Cond: (partner_id = 359091)
18. 81.456 173.937 ↓ 3.8 214,316 1

HashAggregate (cost=20,107.63..20,664.63 rows=55,700 width=4) (actual time=143.322..173.937 rows=214,316 loops=1)

  • Group Key: ph.client_id
19. 79.372 92.481 ↓ 3.8 214,316 1

Bitmap Heap Scan on partner_attachment_history ph (cost=4,418.61..19,967.29 rows=56,138 width=4) (actual time=14.346..92.481 rows=214,316 loops=1)

  • Recheck Cond: (partner_id = 359091)
  • Filter: (CASE WHEN (detached_at IS NOT NULL) THEN (detached_at >= '2020-02-01 00:00:00'::timestamp without time zone) ELSE true END AND CASE WHEN (attached_at IS NOT NULL) THEN (attached_at <= '2020-02-12 23:59:59'::timestamp without time zone) ELSE true END)
  • Rows Removed by Filter: 658
  • Heap Blocks: exact=9793
20. 13.109 13.109 ↑ 1.0 214,974 1

Bitmap Index Scan on partner_attachment_history_idx_partner_id (cost=0.00..4,404.57 rows=224,553 width=0) (actual time=13.109..13.109 rows=214,974 loops=1)

  • Index Cond: (partner_id = 359091)
21. 857.412 857.412 ↑ 2.0 2 214,353

Index Scan using account_user_id_idx on account a (cost=0.56..10.18 rows=4 width=23) (actual time=0.003..0.004 rows=2 loops=214,353)

  • Index Cond: (user_id = ah.client_id)
  • Filter: CASE WHEN (ah.detached_at IS NOT NULL) THEN (created_dt <= ah.detached_at) ELSE true END
  • Rows Removed by Filter: 0
22. 0.003 0.008 ↑ 1.0 14 1

Hash (cost=1.19..1.19 rows=14 width=4) (actual time=0.008..0.008 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.005 0.005 ↑ 1.0 14 1

Seq Scan on account_platforms ap (cost=0.00..1.19 rows=14 width=4) (actual time=0.004..0.005 rows=14 loops=1)

  • Filter: ((name)::text <> 'mt5_real_contest'::text)
  • Rows Removed by Filter: 1
24. 1,311.075 1,311.075 ↑ 1.0 1 437,025

Index Scan using account_mt4_pkey on account_mt4 a_mt4 (cost=0.56..0.78 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=437,025)

  • Index Cond: (account_id = a.id)
  • Filter: (server = 'dollar'::type.account_server)
  • Rows Removed by Filter: 0
25. 0.006 0.012 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=11) (actual time=0.012..0.012 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.006 0.006 ↑ 1.0 24 1

Seq Scan on account_types a_t (cost=0.00..1.24 rows=24 width=11) (actual time=0.004..0.006 rows=24 loops=1)

27. 2,093.580 4,385.022 ↓ 1.0 13,773,249 1

Hash (cost=391,063.22..391,063.22 rows=13,769,822 width=8) (actual time=4,385.022..4,385.022 rows=13,773,249 loops=1)

  • Buckets: 8388608 Batches: 4 Memory Usage: 200027kB
28. 2,291.442 2,291.442 ↓ 1.0 13,773,249 1

Seq Scan on "user" pu (cost=0.00..391,063.22 rows=13,769,822 width=8) (actual time=0.010..2,291.442 rows=13,773,249 loops=1)

29. 0.280 181.194 ↓ 36.7 1,393 1

Hash (cost=131,530.17..131,530.17 rows=38 width=132) (actual time=181.194..181.194 rows=1,393 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 90kB
30. 0.096 180.914 ↓ 36.7 1,393 1

Subquery Scan on stat (cost=131,528.94..131,530.17 rows=38 width=132) (actual time=180.079..180.914 rows=1,393 loops=1)

31. 5.452 180.818 ↓ 36.7 1,393 1

HashAggregate (cost=131,528.94..131,529.79 rows=38 width=16) (actual time=180.078..180.818 rows=1,393 loops=1)

  • Group Key: s.server_account_number
32. 175.366 175.366 ↑ 1.4 5,542 1

Index Scan using arrg_account_stats_date_idx on arrg_account_stats s (cost=0.56..131,415.64 rows=7,553 width=16) (actual time=0.146..175.366 rows=5,542 loops=1)

  • Index Cond: ((date >= '2020-02-01'::date) AND (date <= '2020-02-12'::date))
  • Filter: (partner_id = 359091)
  • Rows Removed by Filter: 510210
Planning time : 23.904 ms
Execution time : 10,526.218 ms