explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zi5j

Settings
# exclusive inclusive rows x rows loops node
1. 0.844 2,964.097 ↑ 285.8 269 1

Nested Loop (cost=60,636.96..90,373.33 rows=76,867 width=517) (actual time=650.645..2,964.097 rows=269 loops=1)

2. 0.052 0.052 ↑ 1.0 1 1

Seq Scan on "Banks" (cost=0.00..7.01 rows=1 width=4) (actual time=0.050..0.052 rows=1 loops=1)

  • Filter: ("BankId" = 1)
3. 2,184.503 2,963.201 ↑ 285.8 269 1

Hash Right Join (cost=60,636.96..89,597.65 rows=76,867 width=517) (actual time=650.588..2,963.201 rows=269 loops=1)

  • Hash Cond: (("CustomerAddresses"."BankId" = "Customers"."BankId") AND ("CustomerAddresses"."CustomerId" = "Customers"."CustomerId"))
  • Filter: ((lower(("Customers"."CustomerNumber")::text) ~~ '%turner%'::text) OR (("Customers"."TaxId")::text ~~ '%turner%'::text) OR (lower(("Customers"."FullName")::text) ~~ '%turner%'::text) OR (lower(("Customers"."FirstName")::text) ~~ '%turner%'::text) OR (lower(("Customers"."MiddleName")::text) ~~ '%turner%'::text) OR (lower(("Customers"."LastName")::text) ~~ '%turner%'::text) OR (lower(("CustomerAddresses"."EmailAddress1")::text) ~~ '%turner%'::text) OR (lower(("CustomerAddresses"."EmailAddress2")::text) ~~ '%turner%'::text) OR (lower(("CustomerAddresses"."HomePhone")::text) ~~ '%turner%'::text) OR (lower(("CustomerAddresses"."WorkPhone")::text) ~~ '%turner%'::text) OR (lower(("CustomerAddresses"."MobilePhone")::text) ~~ '%turner%'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
  • Rows Removed by Filter: 149765
4. 138.645 138.645 ↑ 1.0 150,030 1

Seq Scan on "CustomerAddresses" (cost=0.00..13,197.47 rows=150,031 width=110) (actual time=0.014..138.645 rows=150,030 loops=1)

  • Filter: (("BankId" = 1) AND ("AddressIndex" = 0))
  • Rows Removed by Filter: 1
5. 138.022 504.965 ↑ 1.0 150,034 1

Hash (cost=48,396.53..48,396.53 rows=150,362 width=517) (actual time=504.965..504.965 rows=150,034 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 4624kB
6. 366.943 366.943 ↑ 1.0 150,034 1

Seq Scan on "Customers" (cost=0.00..48,396.53 rows=150,362 width=517) (actual time=0.056..366.943 rows=150,034 loops=1)

  • Filter: ("BankId" = 1)
7.          

SubPlan (forHash Right Join)

8. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_customerrelationships_covering2 on "CustomerRelationships" (cost=0.42..8.50 rows=1 width=0) (never executed)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
  • Filter: (("AccountNumber")::text ~~ '%turner%'::text)
  • Heap Fetches: 0
9. 22.068 135.088 ↓ 0.0 0 1

Gather (cost=1,000.42..17,512.03 rows=51 width=4) (actual time=135.039..135.088 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
10. 113.020 113.020 ↓ 0.0 0 2

Parallel Index Only Scan using idx_customerrelationships_covering2 on "CustomerRelationships" "CustomerRelationships_1" (cost=0.42..16,506.93 rows=30 width=4) (actual time=113.020..113.020 rows=0 loops=2)

  • Filter: (("AccountNumber")::text ~~ '%turner%'::text)
  • Rows Removed by Filter: 257530
  • Heap Fetches: 0
Planning time : 24.426 ms