explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6HwM

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 813.341 ↑ 1.1 87 1

Limit (cost=39,145.03..39,145.28 rows=100 width=574) (actual time=813.332..813.341 rows=87 loops=1)

2. 1.027 813.334 ↑ 361.2 87 1

Sort (cost=39,145.03..39,223.60 rows=31,426 width=574) (actual time=813.330..813.334 rows=87 loops=1)

  • Sort Key: "Banks"."Name", "Customers"."FullName
  • Sort Method: quicksort Memory: 104kB
3. 0.174 812.307 ↑ 361.2 87 1

Nested Loop (cost=27,406.05..37,943.95 rows=31,426 width=574) (actual time=236.857..812.307 rows=87 loops=1)

4. 0.016 0.016 ↑ 1.0 1 1

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

  • Filter: ("BankId" = 1)
5. 554.472 812.117 ↑ 361.2 87 1

Hash Right Join (cost=27,406.05..37,627.68 rows=31,426 width=564) (actual time=236.838..812.117 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
6. 26.083 26.083 ↓ 1.0 50,004 1

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

  • Filter: ("AddressIndex" = 0)
  • Rows Removed by Filter: 1
7. 54.461 178.860 ↑ 1.3 50,004 1

Hash (cost=22,082.03..22,082.03 rows=62,802 width=564) (actual time=178.860..178.860 rows=50,004 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 3837kB
8. 124.399 124.399 ↑ 1.3 50,004 1

Seq Scan on "Customers" (cost=0.00..22,082.03 rows=62,802 width=564) (actual time=0.113..124.399 rows=50,004 loops=1)

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

SubPlan (forHash Right Join)

10. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "CustomerRelationships" (cost=4.45..20.26 rows=1 width=0) (never executed)

  • Recheck Cond: ("CustomerId" = "Customers"."CustomerId")
  • Filter: (("AccountNumber")::text ~~ '%michael%'::text)
11. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_customerrelationships_customerid (cost=0.00..4.45 rows=4 width=0) (never executed)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
12. 52.702 52.702 ↓ 0.0 0 1

Seq Scan on "CustomerRelationships" "CustomerRelationships_1" (cost=0.00..10,881.11 rows=12 width=4) (actual time=52.702..52.702 rows=0 loops=1)

  • Filter: (("AccountNumber")::text ~~ '%michael%'::text)
  • Rows Removed by Filter: 171714
Planning time : 1.269 ms