explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oCi

Settings
# exclusive inclusive rows x rows loops node
1. 4.812 1,258.148 ↑ 3.5 8,609 1

Sort (cost=573,919.28..573,995.48 rows=30,479 width=84) (actual time=1,257.528..1,258.148 rows=8,609 loops=1)

  • Sort Key: (max(p.reg_date)) DESC
  • Sort Method: quicksort Memory: 1997kB
2.          

CTE active_client

3. 13.597 283.278 ↓ 73.6 8,609 1

HashAggregate (cost=83,876.20..83,877.66 rows=117 width=9) (actual time=281.819..283.278 rows=8,609 loops=1)

  • Group Key: stat.partner_id
4. 269.681 269.681 ↓ 1.1 33,526 1

Seq Scan on arrg_partner_stats stat (cost=0.00..83,647.60 rows=30,479 width=9) (actual time=243.582..269.681 rows=33,526 loops=1)

  • Filter: ((date >= '2019-03-10'::date) AND (date <= '2019-03-15'::date))
  • Rows Removed by Filter: 3073406
5. 74.614 1,253.336 ↑ 3.5 8,609 1

HashAggregate (cost=486,933.44..487,771.62 rows=30,479 width=84) (actual time=1,242.296..1,253.336 rows=8,609 loops=1)

  • Group Key: amt.server_account_number
6. 6.824 1,178.722 ↓ 1.1 33,526 1

Nested Loop Left Join (cost=13,066.49..485,790.48 rows=30,479 width=84) (actual time=627.062..1,178.722 rows=33,526 loops=1)

7. 10.715 1,037.794 ↓ 1.1 33,526 1

Hash Left Join (cost=13,066.07..471,939.93 rows=30,479 width=80) (actual time=627.037..1,037.794 rows=33,526 loops=1)

  • Hash Cond: (p.user_id = active_client.partner_id)
8. 10.852 740.535 ↓ 1.1 33,526 1

Hash Join (cost=13,062.26..471,821.72 rows=30,479 width=76) (actual time=340.473..740.535 rows=33,526 loops=1)

  • Hash Cond: (u.country = (c.code_a3)::text)
9. 0.000 729.503 ↓ 1.1 33,526 1

Nested Loop (cost=13,053.71..471,394.07 rows=30,479 width=70) (actual time=340.270..729.503 rows=33,526 loops=1)

10. 7.737 562.673 ↓ 1.1 33,526 1

Nested Loop (cost=13,053.14..247,722.52 rows=30,479 width=70) (actual time=340.260..562.673 rows=33,526 loops=1)

11. 19.546 387.306 ↓ 1.1 33,526 1

Hash Join (cost=13,052.70..97,119.40 rows=30,479 width=42) (actual time=340.235..387.306 rows=33,526 loops=1)

  • Hash Cond: (st_p.partner_id = p.user_id)
12. 280.659 280.659 ↓ 1.1 33,526 1

Seq Scan on arrg_partner_stats st_p (cost=0.00..83,647.60 rows=30,479 width=26) (actual time=251.648..280.659 rows=33,526 loops=1)

  • Filter: ((date >= '2019-03-10'::date) AND (date <= '2019-03-15'::date))
  • Rows Removed by Filter: 3073406
13. 44.556 87.101 ↓ 1.0 337,212 1

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

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

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

15. 167.630 167.630 ↑ 1.0 1 33,526

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=33,526)

  • Index Cond: (id = p.user_id)
16. 167.630 167.630 ↑ 1.0 1 33,526

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=33,526)

  • Index Cond: (account_id = p.account_id)
17. 0.099 0.180 ↑ 1.0 247 1

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

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

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

19. 1.113 286.544 ↓ 73.6 8,609 1

Hash (cost=2.34..2.34 rows=117 width=12) (actual time=286.544..286.544 rows=8,609 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 498kB
20. 285.431 285.431 ↓ 73.6 8,609 1

CTE Scan on active_client (cost=0.00..2.34 rows=117 width=12) (actual time=281.823..285.431 rows=8,609 loops=1)

21. 134.104 134.104 ↓ 0.0 0 33,526

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

  • Index Cond: (user_id = u.id)