explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lyOj

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 1,363.462 ↑ 1.1 87 1

Limit (cost=40,591.14..40,591.39 rows=100 width=576) (actual time=1,363.451..1,363.462 rows=87 loops=1)

2. 1.267 1,363.456 ↑ 287.6 87 1

Sort (cost=40,591.14..40,653.70 rows=25,022 width=576) (actual time=1,363.450..1,363.456 rows=87 loops=1)

  • Sort Key: "Banks"."Name", "Customers"."FullName
  • Sort Method: quicksort Memory: 104kB
3. 1.703 1,362.189 ↑ 287.6 87 1

Nested Loop (cost=30,079.98..39,634.82 rows=25,022 width=576) (actual time=119.956..1,362.189 rows=87 loops=1)

4. 0.042 0.042 ↑ 1.0 1 1

Index Scan using "PK_BankId" on "Banks" (cost=0.13..8.14 rows=1 width=14) (actual time=0.039..0.042 rows=1 loops=1)

  • Index Cond: ("BankId" = 1)
5. 708.474 1,360.444 ↑ 287.6 87 1

Hash Right Join (cost=30,079.85..39,376.46 rows=25,022 width=566) (actual time=119.908..1,360.444 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. 43.225 51.312 ↓ 1.0 50,004 1

Bitmap Heap Scan on "CustomerAddresses" (cost=1,882.81..5,849.84 rows=50,003 width=104) (actual time=8.474..51.312 rows=50,004 loops=1)

  • Recheck Cond: ("AddressIndex" = 0)
  • Heap Blocks: exact=2245
7. 8.087 8.087 ↓ 1.0 50,004 1

Bitmap Index Scan on ix_customeraddresses_customerid_addressindex_zero (cost=0.00..1,870.31 rows=50,003 width=0) (actual time=8.087..8.087 rows=50,004 loops=1)

8. 55.356 101.488 ↑ 1.0 50,004 1

Hash (cost=23,958.00..23,958.00 rows=50,004 width=566) (actual time=101.487..101.488 rows=50,004 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 3837kB
9. 33.394 46.132 ↑ 1.0 50,004 1

Bitmap Heap Scan on "Customers" (cost=2,035.95..23,958.00 rows=50,004 width=566) (actual time=14.377..46.132 rows=50,004 loops=1)

  • Recheck Cond: ("BankId" = 1)
  • Heap Blocks: exact=9498
10. 12.738 12.738 ↑ 1.0 50,004 1

Bitmap Index Scan on idx_customers_covering2 (cost=0.00..2,023.44 rows=50,004 width=0) (actual time=12.738..12.738 rows=50,004 loops=1)

  • Index Cond: ("BankId" = 1)
11.          

SubPlan (forHash Right Join)

12. 299.502 499.170 ↓ 0.0 0 49,917

Bitmap Heap Scan on "CustomerRelationships" (cost=4.45..20.26 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=49,917)

  • Recheck Cond: ("CustomerId" = "Customers"."CustomerId")
  • Filter: (("AccountNumber")::text ~~ '%michael%'::text)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=171113
13. 199.668 199.668 ↑ 1.3 3 49,917

Bitmap Index Scan on ix_customerrelationships_customerid (cost=0.00..4.45 rows=4 width=0) (actual time=0.004..0.004 rows=3 loops=49,917)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on "CustomerRelationships" "CustomerRelationships_1" (cost=10,000,000,000.00..10,000,011,486.42 rows=17 width=4) (never executed)

  • Filter: (("AccountNumber")::text ~~ '%michael%'::text)
Planning time : 1.738 ms