explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Okg

Settings
# exclusive inclusive rows x rows loops node
1. 40.476 2,882.965 ↑ 3.4 8,590 1

GroupAggregate (cost=1,135,140.96..1,137,230.84 rows=28,826 width=116) (actual time=2,840.013..2,882.965 rows=8,590 loops=1)

  • Group Key: c.en, amt.server_account_number, st_p.partner_id, active_client.sum
2. 48.072 2,842.489 ↓ 1.1 32,974 1

Sort (cost=1,135,140.96..1,135,213.02 rows=28,826 width=116) (actual time=2,839.984..2,842.489 rows=32,974 loops=1)

  • Sort Key: c.en, amt.server_account_number, st_p.partner_id, active_client.sum
  • Sort Method: quicksort Memory: 7140kB
3. 16.119 2,794.417 ↓ 1.1 32,974 1

Nested Loop Left Join (cost=681,077.31..1,133,005.66 rows=28,826 width=116) (actual time=2,300.661..2,794.417 rows=32,974 loops=1)

4. 9.213 2,679.376 ↓ 1.1 32,974 1

Hash Left Join (cost=681,076.88..1,119,906.27 rows=28,826 width=112) (actual time=2,300.639..2,679.376 rows=32,974 loops=1)

  • Hash Cond: (p.user_id = active_client.partner_id)
5. 10.358 705.518 ↓ 1.1 32,974 1

Hash Join (cost=13,062.26..451,098.77 rows=28,826 width=76) (actual time=335.980..705.518 rows=32,974 loops=1)

  • Hash Cond: (u.country = (c.code_a3)::text)
6. 24.374 694.989 ↓ 1.1 32,974 1

Nested Loop (cost=13,053.71..450,693.86 rows=28,826 width=70) (actual time=335.797..694.989 rows=32,974 loops=1)

7. 28.844 538.719 ↓ 1.1 32,974 1

Nested Loop (cost=13,053.14..239,185.07 rows=28,826 width=70) (actual time=335.786..538.719 rows=32,974 loops=1)

8. 18.330 377.979 ↓ 1.1 32,974 1

Hash Join (cost=13,052.70..96,755.58 rows=28,826 width=42) (actual time=335.764..377.979 rows=32,974 loops=1)

  • Hash Cond: (st_p.partner_id = p.user_id)
9. 273.042 273.042 ↓ 1.1 32,974 1

Seq Scan on arrg_partner_stats st_p (cost=0.00..83,306.52 rows=28,826 width=26) (actual time=247.741..273.042 rows=32,974 loops=1)

  • Filter: ((date >= '2019-03-10'::date) AND (date <= '2019-03-15'::date))
  • Rows Removed by Filter: 3073406
10. 43.966 86.607 ↓ 1.0 337,204 1

Hash (cost=8,838.98..8,838.98 rows=337,098 width=16) (actual time=86.607..86.607 rows=337,204 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 19903kB
11. 42.641 42.641 ↓ 1.0 337,204 1

Seq Scan on partner p (cost=0.00..8,838.98 rows=337,098 width=16) (actual time=0.006..42.641 rows=337,204 loops=1)

12. 131.896 131.896 ↑ 1.0 1 32,974

Index Scan using users_pkey on users u (cost=0.43..4.93 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=32,974)

  • Index Cond: (id = p.user_id)
13. 131.896 131.896 ↑ 1.0 1 32,974

Index Scan using account_mt4_pkey on account_mt4 amt (cost=0.57..7.33 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=32,974)

  • Index Cond: (account_id = p.account_id)
14. 0.092 0.171 ↑ 1.0 247 1

Hash (cost=5.47..5.47 rows=247 width=14) (actual time=0.171..0.171 rows=247 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
15. 0.079 0.079 ↑ 1.0 247 1

Seq Scan on countries c (cost=0.00..5.47 rows=247 width=14) (actual time=0.010..0.079 rows=247 loops=1)

16. 0.858 1,964.645 ↓ 29.9 5,982 1

Hash (cost=668,012.12..668,012.12 rows=200 width=44) (actual time=1,964.645..1,964.645 rows=5,982 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 368kB
17. 0.425 1,963.787 ↓ 29.9 5,982 1

Subquery Scan on active_client (cost=668,007.62..668,012.12 rows=200 width=44) (actual time=1,962.208..1,963.787 rows=5,982 loops=1)

18. 7.971 1,963.362 ↓ 29.9 5,982 1

HashAggregate (cost=668,007.62..668,010.12 rows=200 width=40) (actual time=1,962.208..1,963.362 rows=5,982 loops=1)

  • Group Key: arrg_client_stats.partner_id
19. 34.277 1,955.391 ↓ 1.8 21,824 1

HashAggregate (cost=667,641.89..667,794.28 rows=12,191 width=12) (actual time=1,950.478..1,955.391 rows=21,824 loops=1)

  • Group Key: arrg_client_stats.client_id, arrg_client_stats.partner_id
20. 1,921.114 1,921.114 ↑ 2.0 59,983 1

Seq Scan on arrg_client_stats (cost=0.00..666,727.60 rows=121,905 width=12) (actual time=1,094.851..1,921.114 rows=59,983 loops=1)

  • Filter: ((date >= '2019-03-10'::date) AND (date <= '2019-03-15'::date) AND (partner_commission > '0'::numeric))
  • Rows Removed by Filter: 21906713
21. 98.922 98.922 ↓ 0.0 0 32,974

Index Scan using loyalty_pkey on loyalty pl (cost=0.42..0.44 rows=1 width=12) (actual time=0.002..0.003 rows=0 loops=32,974)

  • Index Cond: (user_id = u.id)