explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0Dpk

Settings
# exclusive inclusive rows x rows loops node
1. 31.413 5,692.343 ↑ 3.4 8,590 1

GroupAggregate (cost=1,135,136.50..1,137,154.32 rows=28,826 width=116) (actual time=5,658.167..5,692.343 rows=8,590 loops=1)

  • Group Key: c.en, amt.server_account_number, st_p.partner_id, u.id, u.is_vip, p.reg_date, u.name, pl.level, active_client.count, active_client.sum
2. 60.447 5,660.930 ↓ 1.1 32,974 1

Sort (cost=1,135,136.50..1,135,208.56 rows=28,826 width=116) (actual time=5,658.138..5,660.930 rows=32,974 loops=1)

  • Sort Key: c.en, amt.server_account_number, st_p.partner_id, u.id, u.is_vip, p.reg_date, u.name, pl.level, active_client.count, active_client.sum
  • Sort Method: quicksort Memory: 7387kB
3. 5.464 5,600.483 ↓ 1.1 32,974 1

Nested Loop Left Join (cost=681,077.31..1,133,001.20 rows=28,826 width=116) (actual time=4,729.091..5,600.483 rows=32,974 loops=1)

4. 11.070 5,430.149 ↓ 1.1 32,974 1

Hash Left Join (cost=681,076.88..1,119,901.83 rows=28,826 width=112) (actual time=4,729.014..5,430.149 rows=32,974 loops=1)

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

Hash Join (cost=13,062.26..451,094.33 rows=28,826 width=76) (actual time=634.169..1,324.252 rows=32,974 loops=1)

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

Nested Loop (cost=13,053.71..450,689.41 rows=28,826 width=70) (actual time=634.080..1,312.311 rows=32,974 loops=1)

7. 10.705 1,004.648 ↓ 1.1 32,974 1

Nested Loop (cost=13,053.14..239,184.39 rows=28,826 width=70) (actual time=634.029..1,004.648 rows=32,974 loops=1)

8. 21.144 697.177 ↓ 1.1 32,974 1

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

  • Hash Cond: (st_p.partner_id = p.user_id)
9. 556.137 556.137 ↓ 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=512.580..556.137 rows=32,974 loops=1)

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

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

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

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

12. 296.766 296.766 ↑ 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.008..0.009 rows=1 loops=32,974)

  • Index Cond: (id = p.user_id)
13. 296.766 296.766 ↑ 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.008..0.009 rows=1 loops=32,974)

  • Index Cond: (account_id = p.account_id)
14. 0.046 0.076 ↑ 1.0 247 1

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

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

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

16. 0.916 4,094.827 ↓ 29.9 5,982 1

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

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

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

18. 7.857 4,093.510 ↓ 29.9 5,982 1

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

  • Group Key: arrg_client_stats.partner_id
19. 38.842 4,085.653 ↓ 1.8 21,824 1

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

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

Seq Scan on arrg_client_stats (cost=0.00..666,727.60 rows=121,905 width=12) (actual time=2,310.708..4,046.811 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. 164.870 164.870 ↓ 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.003..0.005 rows=0 loops=32,974)

  • Index Cond: (user_id = u.id)
Planning time : 6.237 ms
Execution time : 5,693.801 ms