explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EDzu

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

GroupAggregate (cost=1,201,871.88..1,203,889.70 rows=28,826 width=84) (actual time=2,851.844..2,892.274 rows=8,590 loops=1)

  • Group Key: c.en, amt.server_account_number, st_p.partner_id
2.          

CTE active_client

3. 23.628 1,957.771 ↓ 427.3 5,982 1

HashAggregate (cost=727,073.72..727,073.90 rows=14 width=12) (actual time=1,956.379..1,957.771 rows=5,982 loops=1)

  • Group Key: arrg_client_stats.partner_id
4. 1,934.143 1,934.143 ↑ 1.1 59,983 1

Seq Scan on arrg_client_stats (cost=0.00..726,596.40 rows=63,643 width=12) (actual time=1,098.701..1,934.143 rows=59,983 loops=1)

  • Filter: ((date >= '2019-03-10'::date) AND (date <= '2019-03-15'::date) AND (partner_id > 0) AND (partner_commission > '0'::numeric))
  • Rows Removed by Filter: 21906713
5. 44.558 2,854.387 ↓ 1.1 32,974 1

Sort (cost=474,797.99..474,870.05 rows=28,826 width=84) (actual time=2,851.816..2,854.387 rows=32,974 loops=1)

  • Sort Key: c.en, amt.server_account_number, st_p.partner_id
  • Sort Method: quicksort Memory: 6553kB
6. 23.340 2,809.829 ↓ 1.1 32,974 1

Nested Loop Left Join (cost=13,063.14..472,662.69 rows=28,826 width=84) (actual time=2,296.436..2,809.829 rows=32,974 loops=1)

7. 9.177 2,687.567 ↓ 1.1 32,974 1

Hash Left Join (cost=13,062.72..459,563.31 rows=28,826 width=80) (actual time=2,296.414..2,687.567 rows=32,974 loops=1)

  • Hash Cond: (p.user_id = active_client.partner_id)
8. 10.529 718.377 ↓ 1.1 32,974 1

Hash Join (cost=13,062.26..459,454.74 rows=28,826 width=76) (actual time=336.384..718.377 rows=32,974 loops=1)

  • Hash Cond: (u.country = (c.code_a3)::text)
9. 0.000 707.661 ↓ 1.1 32,974 1

Nested Loop (cost=13,053.71..459,049.83 rows=28,826 width=70) (actual time=336.187..707.661 rows=32,974 loops=1)

10. 1.121 547.198 ↓ 1.1 32,974 1

Nested Loop (cost=13,053.14..247,533.86 rows=28,826 width=70) (actual time=336.177..547.198 rows=32,974 loops=1)

11. 18.873 381.207 ↓ 1.1 32,974 1

Hash Join (cost=13,052.70..105,103.00 rows=28,826 width=42) (actual time=336.153..381.207 rows=32,974 loops=1)

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

Seq Scan on arrg_partner_stats st_p (cost=0.00..91,653.94 rows=28,826 width=26) (actual time=247.160..274.766 rows=32,974 loops=1)

  • Filter: ((date >= '2019-03-10'::date) AND (date <= '2019-03-15'::date) AND (partner_id > 0))
  • Rows Removed by Filter: 3073406
13. 44.898 87.568 ↓ 1.0 337,205 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 19903kB
14. 42.670 42.670 ↓ 1.0 337,205 1

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

15. 164.870 164.870 ↑ 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.005 rows=1 loops=32,974)

  • Index Cond: (id = p.user_id)
16. 164.870 164.870 ↑ 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.005 rows=1 loops=32,974)

  • Index Cond: (account_id = p.account_id)
17. 0.091 0.187 ↑ 1.0 247 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
18. 0.096 0.096 ↑ 1.0 247 1

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

19. 0.721 1,960.013 ↓ 427.3 5,982 1

Hash (cost=0.28..0.28 rows=14 width=12) (actual time=1,960.013..1,960.013 rows=5,982 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 322kB
20. 1,959.292 1,959.292 ↓ 427.3 5,982 1

CTE Scan on active_client (cost=0.00..0.28 rows=14 width=12) (actual time=1,956.384..1,959.292 rows=5,982 loops=1)

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)