explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gwhs : select count(distinct customer0_.CUSTOMERID) as col_0_0_ from CAM_CUSTOMER customer0_ inner join CAM_PAYMENTCONTRACT paymentcon1_ on customer0_.CUSTOMERID=paymentcon1_.CUSTOMERID inner join CAM_TENANTRELATION tenantrela2_ on paymentcon1_.PAYMENTCONTRACTID=tenantrela2_.PAYMENTCONTRACTID inner join PERSON person3_ on customer0_.PERSONID=person3_.PERSONID left outer join WEBACCOUNT webaccount4_ on person3_.PERSONID=webaccount4_.PERSONID left outer join WEBACCOUNTOPTIN webaccount5_ on webaccount4_.WEBACCOUNTID=webaccount5_.WEBACCOUNTID left outer join ADDRESS addresses6_ on person3_.PERSONID=addresses6_.PERSONID and (addresses6_.ADDRESSTYPE=$1) left outer join PERSONATTRIBUTE personattr7_ on person3_.PERSONID=personattr7_.PERSONID and (personattr7_.ATTRIBUTETYPEID=$2) where (customer0_.DELETEDATE is null) and (paymentcon1_.DELETEDATE is null) and (person3_.DELETEDATE is null) and customer0_.STATUS<>$3 and tenantrela2_.TENANTID=$4

Settings
# exclusive inclusive rows x rows loops node
1. 188.477 5,525.942 ↑ 1.0 1 1

Aggregate (cost=7,244.13..7,244.14 rows=1 width=8) (actual time=5,525.937..5,525.942 rows=1 loops=1)

2. 332.610 5,337.465 ↓ 2.0 15,021 1

Hash Left Join (cost=5,191.35..7,225.58 rows=7,421 width=8) (actual time=3,091.825..5,337.465 rows=15,021 loops=1)

  • Hash Cond: (person3_.personid = webaccount4_.personid)
3. 1,145.222 5,004.528 ↓ 2.0 15,021 1

Hash Right Join (cost=5,189.05..7,195.44 rows=7,421 width=16) (actual time=3,091.469..5,004.528 rows=15,021 loops=1)

  • Hash Cond: (addresses6_.personid = person3_.personid)
4. 768.081 768.081 ↓ 2.0 59,615 1

Seq Scan on address addresses6_ (cost=0.00..1,872.20 rows=29,608 width=8) (actual time=0.029..768.081 rows=59,615 loops=1)

  • Filter: ((addresstype)::text = ($1)::text)
  • Rows Removed by Filter: 313
5. 156.524 3,091.225 ↓ 2.0 15,021 1

Hash (cost=5,096.29..5,096.29 rows=7,421 width=16) (actual time=3,091.225..3,091.225 rows=15,021 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 833kB
6. 1,069.137 2,934.701 ↓ 2.0 15,021 1

Hash Join (cost=2,455.41..5,096.29 rows=7,421 width=16) (actual time=1,013.294..2,934.701 rows=15,021 loops=1)

  • Hash Cond: (person3_.personid = customer0_.personid)
7. 857.478 857.478 ↓ 1.0 96,498 1

Seq Scan on person person3_ (cost=0.00..2,210.85 rows=94,885 width=8) (actual time=0.022..857.478 rows=96,498 loops=1)

  • Filter: (deletedate IS NULL)
  • Rows Removed by Filter: 2
8. 107.901 1,008.086 ↓ 2.0 15,021 1

Hash (cost=2,362.65..2,362.65 rows=7,421 width=16) (actual time=1,008.086..1,008.086 rows=15,021 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 833kB
9. 218.945 900.185 ↓ 2.0 15,021 1

Hash Join (cost=1,621.27..2,362.65 rows=7,421 width=16) (actual time=565.703..900.185 rows=15,021 loops=1)

  • Hash Cond: (tenantrela2_.paymentcontractid = paymentcon1_.paymentcontractid)
10. 115.603 115.603 ↓ 1.0 15,021 1

Seq Scan on cam_tenantrelation tenantrela2_ (cost=0.00..611.51 rows=14,841 width=8) (actual time=0.024..115.603 rows=15,021 loops=1)

  • Filter: (tenantid = $4)
11. 102.528 565.637 ↓ 2.0 15,021 1

Hash (cost=1,528.51..1,528.51 rows=7,421 width=24) (actual time=565.637..565.637 rows=15,021 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 950kB
12. 186.266 463.109 ↓ 2.0 15,021 1

Hash Join (cost=747.24..1,528.51 rows=7,421 width=24) (actual time=177.059..463.109 rows=15,021 loops=1)

  • Hash Cond: (paymentcon1_.customerid = customer0_.customerid)
13. 99.842 99.842 ↓ 1.0 15,021 1

Seq Scan on cam_paymentcontract paymentcon1_ (cost=0.00..651.41 rows=14,841 width=16) (actual time=0.020..99.842 rows=15,021 loops=1)

  • Filter: (deletedate IS NULL)
14. 85.374 177.001 ↓ 2.0 15,019 1

Hash (cost=654.49..654.49 rows=7,420 width=16) (actual time=177.001..177.001 rows=15,019 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 833kB
15. 91.627 91.627 ↓ 2.0 15,019 1

Seq Scan on cam_customer customer0_ (cost=0.00..654.49 rows=7,420 width=16) (actual time=0.021..91.627 rows=15,019 loops=1)

  • Filter: ((deletedate IS NULL) AND ((status)::text <> ($3)::text))
16. 0.049 0.327 ↓ 1.2 6 1

Hash (cost=2.23..2.23 rows=5 width=8) (actual time=0.327..0.327 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.115 0.278 ↓ 1.2 6 1

Hash Left Join (cost=1.11..2.23 rows=5 width=8) (actual time=0.164..0.278 rows=6 loops=1)

  • Hash Cond: (webaccount4_.webaccountid = webaccount5_.webaccountid)
18. 0.058 0.058 ↓ 1.2 6 1

Seq Scan on webaccount webaccount4_ (cost=0.00..1.05 rows=5 width=16) (actual time=0.023..0.058 rows=6 loops=1)

19. 0.051 0.105 ↓ 1.2 6 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.105..0.105 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.054 0.054 ↓ 1.2 6 1

Seq Scan on webaccountoptin webaccount5_ (cost=0.00..1.05 rows=5 width=8) (actual time=0.020..0.054 rows=6 loops=1)