explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HRi3

Settings
# exclusive inclusive rows x rows loops node
1. 513.938 6,044.301 ↑ 130,437,134.0 1 1

Merge Right Join (cost=53,367.34..5,970,123.45 rows=130,437,134 width=224) (actual time=6,044.274..6,044.301 rows=1 loops=1)

  • Output: c.identifier, concat_ws(' '::text, c.firstname, c.middlename, c.lastname, c.companyname, at.coname), concat_ws(' '::text, a.streetaddress1, a.streetaddress2, a.streetaddress3, a.streetaddress4, a.zipcode, a.city), p.phone, e.email, replace(((((c.firstname)::text || (c.middlename)::text) || (c.lastname)::text) || (c.companyname)::text), ' '::text, ''::text), replace(((((((a.streetaddress1)::text || (a.streetaddress2)::text) || (a.streetaddress3)::text) || (a.streetaddress4)::text) || (a.zipcode)::text) || (a.city)::text), ' '::text, ''::text)
  • Merge Cond: (a.idguid = at.addressguid)
2.          

CTE phone

3. 0.072 2.344 ↓ 13.0 13 1

GroupAggregate (cost=101.96..102.02 rows=1 width=48) (actual time=2.278..2.344 rows=13 loops=1)

  • Output: cci.customerguid, string_agg((cci.contact)::text, ', '::text)
  • Group Key: cci.customerguid
4. 0.073 2.272 ↓ 2.2 13 1

Sort (cost=101.96..101.98 rows=6 width=41) (actual time=2.251..2.272 rows=13 loops=1)

  • Output: cci.customerguid, cci.contact
  • Sort Key: cci.customerguid
  • Sort Method: quicksort Memory: 26kB
5. 2.199 2.199 ↓ 2.2 13 1

Foreign Scan on scdev_cus.customercontactinformations cci (cost=100.00..101.88 rows=6 width=41) (actual time=2.169..2.199 rows=13 loops=1)

  • Output: cci.customerguid, cci.contact
  • Filter: (cci.validtodate > timezone('utc'::text, now()))
  • Rows Removed by Filter: 6
  • Remote SQL: SELECT contact, validtodate, customerguid FROM ele_cus.customercontactinformations WHERE ((typecid = ANY ('{151001,151002}'::integer[])))
6.          

CTE email

7. 0.040 1.959 ↓ 4.0 4 1

GroupAggregate (cost=101.66..101.69 rows=1 width=48) (actual time=1.933..1.959 rows=4 loops=1)

  • Output: cci_1.customerguid, string_agg((cci_1.contact)::text, ', '::text)
  • Group Key: cci_1.customerguid
8. 0.051 1.919 ↓ 1.7 5 1

Sort (cost=101.66..101.67 rows=3 width=41) (actual time=1.910..1.919 rows=5 loops=1)

  • Output: cci_1.customerguid, cci_1.contact
  • Sort Key: cci_1.customerguid
  • Sort Method: quicksort Memory: 25kB
9. 1.868 1.868 ↓ 1.7 5 1

Foreign Scan on scdev_cus.customercontactinformations cci_1 (cost=100.00..101.63 rows=3 width=41) (actual time=1.845..1.868 rows=5 loops=1)

  • Output: cci_1.customerguid, cci_1.contact
  • Filter: (cci_1.validtodate > timezone('utc'::text, now()))
  • Rows Removed by Filter: 5
  • Remote SQL: SELECT contact, validtodate, customerguid FROM ele_cus.customercontactinformations WHERE ((typecid = 151003))
10. 4,898.930 4,898.930 ↑ 1.8 319,981 1

Foreign Scan on scdev_cus.addresses a (cost=100.42..45,649.98 rows=568,304 width=50) (actual time=656.311..4,898.930 rows=319,981 loops=1)

  • Output: a.idguid, a.version, a.datecreated, a.datechanged, a.dataupdater, a.streetaddress1, a.streetaddress2, a.streetaddress3, a.city, a.zipcode, a.county, a.countrycode, a.fulltextindex, a.streetaddress4, a.datacreator, a.pobox
  • Remote SQL: SELECT idguid, streetaddress1, streetaddress2, streetaddress3, city, zipcode, streetaddress4 FROM ele_cus.addresses ORDER BY idguid ASC NULLS LAST
11. 0.022 631.433 ↑ 45,904.0 1 1

Materialize (cost=53,063.21..53,292.73 rows=45,904 width=272) (actual time=631.421..631.433 rows=1 loops=1)

  • Output: c.identifier, c.firstname, c.middlename, c.lastname, c.companyname, at.coname, at.addressguid, p.phone, e.email
12. 0.038 631.411 ↑ 45,904.0 1 1

Sort (cost=53,063.21..53,177.97 rows=45,904 width=272) (actual time=631.405..631.411 rows=1 loops=1)

  • Output: c.identifier, c.firstname, c.middlename, c.lastname, c.companyname, at.coname, at.addressguid, p.phone, e.email
  • Sort Key: at.addressguid
  • Sort Method: quicksort Memory: 25kB
13. 0.019 631.373 ↑ 45,904.0 1 1

Nested Loop (cost=200.50..43,702.28 rows=45,904 width=272) (actual time=631.355..631.373 rows=1 loops=1)

  • Output: c.identifier, c.firstname, c.middlename, c.lastname, c.companyname, at.coname, at.addressguid, p.phone, e.email
14. 0.024 629.664 ↑ 51.0 1 1

Hash Left Join (cost=100.06..37,552.96 rows=51 width=240) (actual time=629.652..629.664 rows=1 loops=1)

  • Output: c.identifier, c.firstname, c.middlename, c.lastname, c.companyname, c.idguid, p.phone, e.email
  • Hash Cond: (c.idguid = e.customerguid)
15. 0.047 627.635 ↑ 51.0 1 1

Hash Left Join (cost=100.03..37,552.72 rows=51 width=208) (actual time=627.626..627.635 rows=1 loops=1)

  • Output: c.identifier, c.firstname, c.middlename, c.lastname, c.companyname, c.idguid, p.phone
  • Hash Cond: (c.idguid = p.customerguid)
16. 625.129 625.129 ↑ 51.0 1 1

Foreign Scan on scdev_cus.customers c (cost=100.00..37,552.49 rows=51 width=45) (actual time=625.125..625.129 rows=1 loops=1)

  • Output: c.idguid, c.version, c.datecreated, c.datechanged, c.dataupdater, c.customerkey, c.identifier, c.typecid, c.statuscid, c.companyname, c.lastname, c.firstname, c.dod, c.isestate, c.vatidentifier, c.bankcruptdate, c.counterpartnerkey, c.languagecid, c.additionalinformation, c.onlineusername, c.additionalorganisationidentifier, c.issecret, c.communicationchannels, c.marketingchannels, c.ismarketingrestricted, c.parentcustomeridentifierguid, c.fulltextindex, c.isconsolidatedinvoicerequested, c.directmarketingcid, c.researchprohibitedcid, c.iselectricityoversensitive, c.isprotectionordercustomer, c.datacreator, c.additionalidentifier, c.identifiertypecid, c.birthdate, c.middlename, c.nationalitycid, c.haspermissiontouseelectroniccommunication, c.haspermissiontouseelectronicmarketing, c.isfrozen, c.accountgroupcid
  • Remote SQL: SELECT idguid, identifier, companyname, lastname, firstname, middlename FROM ele_cus.customers WHERE ((replace((((firstname || middlename) || lastname) || companyname), ' '::text, ''::text) ~~* '%aira%ridanpää%'::text))
17. 0.036 2.459 ↓ 13.0 13 1

Hash (cost=0.02..0.02 rows=1 width=48) (actual time=2.456..2.459 rows=13 loops=1)

  • Output: p.phone, p.customerguid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 2.423 2.423 ↓ 13.0 13 1

CTE Scan on phone p (cost=0.00..0.02 rows=1 width=48) (actual time=2.289..2.423 rows=13 loops=1)

  • Output: p.phone, p.customerguid
19. 0.021 2.005 ↓ 4.0 4 1

Hash (cost=0.02..0.02 rows=1 width=48) (actual time=2.003..2.005 rows=4 loops=1)

  • Output: e.email, e.customerguid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 1.984 1.984 ↓ 4.0 4 1

CTE Scan on email e (cost=0.00..0.02 rows=1 width=48) (actual time=1.943..1.984 rows=4 loops=1)

  • Output: e.email, e.customerguid
21. 1.690 1.690 ↑ 1.0 1 1

Foreign Scan on scdev_cus.addresstypes at (cost=100.43..120.56 rows=1 width=72) (actual time=1.686..1.690 rows=1 loops=1)

  • Output: at.idguid, at.version, at.datecreated, at.datechanged, at.dataupdater, at.addresstypekey, at.addressclassificationcid, at.purposeofuse, at.coname, at.validfromdate, at.validtodate, at.customerguid, at.addressguid, at.einvoiceaddressguid, at.fulltextindex, at.datacreator
  • Filter: (at.validtodate > timezone('utc'::text, now()))
  • Remote SQL: SELECT coname, validtodate, customerguid, addressguid FROM ele_cus.addresstypes WHERE ((addressclassificationcid = 152001)) AND (($1::uuid = customerguid))
Planning time : 22.613 ms