explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pWrB

Settings
# exclusive inclusive rows x rows loops node
1. 0.656 8,229.326 ↑ 275.9 269 1

Nested Loop (cost=60,408.99..89,969.94 rows=74,227 width=517) (actual time=4,315.296..8,229.326 rows=269 loops=1)

2. 0.039 0.039 ↑ 1.0 1 1

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

  • Filter: ("BankId" = 1)
3. 2,612.737 8,228.631 ↑ 275.9 269 1

Hash Right Join (cost=60,408.99..89,220.66 rows=74,227 width=517) (actual time=4,315.256..8,228.631 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. 1,339.588 1,339.588 ↓ 1.0 150,030 1

Seq Scan on "CustomerAddresses" (cost=0.00..13,197.47 rows=150,026 width=110) (actual time=0.588..1,339.588 rows=150,030 loops=1)

  • Filter: (("BankId" = 1) AND ("AddressIndex" = 0))
  • Rows Removed by Filter: 1
5. 151.910 4,116.150 ↓ 1.0 150,034 1

Hash (cost=48,366.09..48,366.09 rows=147,927 width=517) (actual time=4,116.150..4,116.150 rows=150,034 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 4624kB
6. 3,964.240 3,964.240 ↓ 1.0 150,034 1

Seq Scan on "Customers" (cost=0.00..48,366.09 rows=147,927 width=517) (actual time=0.008..3,964.240 rows=150,034 loops=1)

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

SubPlan (forHash Right Join)

8. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_customerrelationships_customerid on "CustomerRelationships" (cost=0.42..20.50 rows=1 width=0) (never executed)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
  • Filter: (("AccountNumber")::text ~~ '%turner%'::text)
9. 27.236 160.156 ↓ 0.0 0 1

Gather (cost=1,000.00..33,016.31 rows=51 width=4) (actual time=160.112..160.156 rows=0 loops=1)

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

Parallel Seq Scan on "CustomerRelationships" "CustomerRelationships_1" (cost=0.00..32,011.21 rows=30 width=4) (actual time=132.920..132.920 rows=0 loops=2)

  • Filter: (("AccountNumber")::text ~~ '%turner%'::text)
  • Rows Removed by Filter: 257530
Planning time : 8.517 ms