explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 4f3

options
# exclusive inclusive rows x rows loops node
1. 0.068 15203.791 ↑ 3399.3 3 1
→

HashAggregate (cost=57080.35..57182.33 rows=10198 width=25) (actual time=15203.769..15203.791 rows=3 loops=1)

2. 12.054 15203.723 ↑ 784.5 13 1
→

Hash Join (cost=45360.04..57029.36 rows=10198 width=25) (actual time=15178.313..15203.723 rows=13 loops=1)

  • Hash Cond: (c.contactid = a.fkcontactid)
3. 0.562 36.596 ↑ 4365.5 370 1
→

Nested Loop (cost=11907.76..17418.02 rows=1615223 width=25) (actual time=17.883..36.596 rows=370 loops=1)

4. 0.246 17.606 ↓ 1.4 271 1
→

HashAggregate (cost=11907.76..11909.76 rows=200 width=24) (actual time=17.504..17.606 rows=271 loops=1)

5. 3.520 17.360 ↑ 5.5 271 1
→

HashAggregate (cost=11870.41..11889.09 rows=1494 width=24) (actual time=16.797..17.360 rows=271 loops=1)

  • Filter: (count(1) > 2)
6. 4.485 13.840 ↓ 3.4 5051 1
→

Hash Join (cost=649.32..11862.94 rows=1494 width=24) (actual time=5.383..13.840 rows=5051 loops=1)

  • Hash Cond: (eb.fkemailbouncetypeid = ebt.emailbouncetypeid)
7. 4.089 9.340 ↑ 1.0 8958 1
→

Bitmap Heap Scan on emailbounce eb (cost=648.23..11813.31 rows=8961 width=28) (actual time=5.350..9.340 rows=8958 loops=1)

  • Recheck Cond: ((entrydate >= (date_trunc('day'::text, now()) - '30 days'::interval)) AND (entrydate < (date_trunc('day'::text, now()) -
8. 5.251 5.251 ↑ 1.0 8958 1
→

Bitmap Index Scan on idx_emailbounce_entrydate (cost=0.00..645.99 rows=8961 width=0) (actual time=5.251..5.251 rows=8958 loops=1)

  • Index Cond: ((entrydate >= (date_trunc('day'::text, now()) - '30 days'::interval)) AND (entrydate < (date_trunc('day'::text, now(
9. 0.004 0.015 ↑ 1.0 1 1
→

Hash (cost=1.07..1.07 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
10. 0.011 0.011 ↑ 1.0 1 1
→

Seq Scan on emailbouncetype ebt (cost=0.00..1.07 rows=1 width=4) (actual time=0.009..0.011 rows=1 loops=1)

  • Filter: (type = 'hard'::text)
11. 18.428 18.428 ↑ 6.0 1 271
→

Index Scan using idx_contact_email on contact c (cost=0.00..27.47 rows=6 width=25) (actual time=0.063..0.068 rows=1 loops=271)

  • Index Cond: (email = eb.email)
12. 446.132 15155.073 ↓ 31.5 830927 1
→

Hash (cost=33122.15..33122.15 rows=26410 width=8) (actual time=15155.073..15155.073 rows=830927 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 22721kB
13. 769.030 14708.941 ↓ 31.5 830927 1
→

Nested Loop (cost=647.53..33122.15 rows=26410 width=8) (actual time=800.993..14708.941 rows=830927 loops=1)

14. 985.028 6461.568 ↓ 31.5 830927 1
→

Hash Join (cost=647.53..18549.02 rows=26410 width=4) (actual time=800.958..6461.568 rows=830927 loops=1)

  • Hash Cond: (si.fkserviceinstancestatusid = sis.serviceinstancestatusid)
15. 1248.483 5476.524 ↓ 13.1 2074164 1
→

Nested Loop (cost=646.44..17689.60 rows=158461 width=8) (actual time=800.925..5476.524 rows=2074164 loops=1)

16. 0.077 0.131 ↑ 2.5 2 1
→

Hash Join (cost=1.56..5.50 rows=5 width=4) (actual time=0.032..0.131 rows=2 loops=1)

  • Hash Cond: (so.fkserviceid = s.serviceid)
17. 0.037 0.037 ↑ 1.0 137 1
→

Seq Scan on serviceoffering so (cost=0.00..3.37 rows=137 width=8) (actual time=0.005..0.037 rows=137 loops=1)

18. 0.002 0.017 ↑ 1.0 2 1
→

Hash (cost=1.54..1.54 rows=2 width=4) (actual time=0.017..0.017 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.015 0.015 ↑ 1.0 2 1
→

Seq Scan on service s (cost=0.00..1.54 rows=2 width=4) (actual time=0.010..0.015 rows=2 loops=1)

  • Filter: emailsubscription
20. 4227.910 4227.910 ↓ 22.5 1037082 2
→

Bitmap Heap Scan on serviceinstance si (cost=644.87..2961.76 rows=46005 width=12) (actual time=437.645..2113.955 rows=1037082 loops=2)

  • Recheck Cond: (fkserviceofferingid = so.serviceofferingid)
  • -> Bitmap Index Scan on idx_serviceinstance_fkserviceofferingid2 (cost=0.00..633.37 rows=46005 width=0) (actual time=411.388..411.388
  • Index Cond: (fkserviceofferingid = so.serviceofferingid)
21. 0.003 0.016 ↑ 1.0 1 1
→

Hash (cost=1.07..1.07 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.013 0.013 ↑ 1.0 1 1
→

Seq Scan on serviceinstancestatus sis (cost=0.00..1.07 rows=1 width=4) (actual time=0.011..0.013 rows=1 loops=1)

  • Filter: (status = 'ACTIVE'::text)
23. 7478.343 7478.343 ↑ 1.0 1 830927
→

Index Scan using idx_account_accountid on account a (cost=0.00..0.54 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=830927)

  • Index Cond: (accountid = si.fkaccountid)