explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T4H8

Settings
# exclusive inclusive rows x rows loops node
1. 0.989 817.861 ↑ 287.6 87 1

Sort (cost=43,937.67..44,000.23 rows=25,022 width=575) (actual time=817.857..817.861 rows=87 loops=1)

  • Sort Key: "Banks"."Name", "Customers"."FullName
  • Sort Method: quicksort Memory: 104kB
2. 0.174 816.872 ↑ 287.6 87 1

Nested Loop (cost=26,161.10..35,694.20 rows=25,022 width=575) (actual time=274.741..816.872 rows=87 loops=1)

3. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on "Banks" (cost=0.00..2.01 rows=1 width=14) (actual time=0.017..0.020 rows=1 loops=1)

  • Filter: ("BankId" = 1)
4. 509.438 816.678 ↑ 287.6 87 1

Hash Right Join (cost=26,161.10..35,441.97 rows=25,022 width=565) (actual time=274.719..816.678 rows=87 loops=1)

  • Hash Cond: ("CustomerAddresses"."CustomerId" = "Customers"."CustomerId")
  • Filter: ((lower(("Customers"."CustomerNumber")::text) ~~ '%michael%'::text) OR (("Customers"."TaxId")::text ~~ '%michael%'::text) OR (lower(("Customers"."FullName")::text) ~~ '%michael%'::text) OR (lower(("CustomerAddresses"."EmailAddress1")::text) ~~ '%michael%'::text) OR (lower(("CustomerAddresses"."EmailAddress2")::text) ~~ '%michael%'::text) OR (lower(("CustomerAddresses"."HomePhone")::text) ~~ '%michael%'::text) OR (lower(("CustomerAddresses"."WorkPhone")::text) ~~ '%michael%'::text) OR (lower(("CustomerAddresses"."MobilePhone")::text) ~~ '%michael%'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
  • Rows Removed by Filter: 49917
5. 37.370 37.370 ↓ 1.0 50,004 1

Seq Scan on "CustomerAddresses" (cost=0.00..3,967.06 rows=50,003 width=104) (actual time=0.012..37.370 rows=50,004 loops=1)

  • Filter: ("AddressIndex" = 0)
  • Rows Removed by Filter: 1
6. 54.744 226.607 ↑ 1.0 50,004 1

Hash (cost=21,922.05..21,922.05 rows=50,004 width=565) (actual time=226.607..226.607 rows=50,004 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 3837kB
7. 171.863 171.863 ↑ 1.0 50,004 1

Seq Scan on "Customers" (cost=0.00..21,922.05 rows=50,004 width=565) (actual time=0.046..171.863 rows=50,004 loops=1)

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

SubPlan (forHash Right Join)

9. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
  • Filter: (("AccountNumber")::text ~~ '%michael%'::text)
  • Heap Fetches: 0
10. 43.263 43.263 ↓ 0.0 0 1

Index Only Scan using idx_customerrelationships_covering2 on "CustomerRelationships" "CustomerRelationships_1" (cost=0.42..6,345.42 rows=17 width=4) (actual time=43.263..43.263 rows=0 loops=1)

  • Filter: (("AccountNumber")::text ~~ '%michael%'::text)
  • Rows Removed by Filter: 171714
  • Heap Fetches: 0
Planning time : 2.985 ms