explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S02G

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 566.017 ↑ 1.1 87 1

Limit (cost=8,406.13..8,406.38 rows=100 width=25) (actual time=566.008..566.017 rows=87 loops=1)

2. 0.690 566.012 ↑ 287.6 87 1

Sort (cost=8,406.13..8,468.68 rows=25,022 width=25) (actual time=566.007..566.012 rows=87 loops=1)

  • Sort Key: "Banks"."Name", "Customers"."FullName
  • Sort Method: quicksort Memory: 31kB
3. 0.090 565.322 ↑ 287.6 87 1

Nested Loop (cost=3,032.15..7,449.80 rows=25,022 width=25) (actual time=99.380..565.322 rows=87 loops=1)

  • Join Filter: ("Customers"."BankId" = "Banks"."BankId")
4. 0.014 0.014 ↑ 1.0 1 1

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

5. 407.613 565.218 ↑ 287.6 87 1

Hash Right Join (cost=3,032.15..7,135.02 rows=25,022 width=19) (actual time=99.365..565.218 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. 63.541 63.541 ↓ 1.0 50,004 1

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

  • Filter: ("AddressIndex" = 0)
  • Rows Removed by Filter: 1
7. 16.748 49.130 ↑ 1.0 50,004 1

Hash (cost=2,407.10..2,407.10 rows=50,004 width=37) (actual time=49.130..49.130 rows=50,004 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4060kB
8. 32.382 32.382 ↑ 1.0 50,004 1

Index Only Scan using idx_customers_covering2 on "Customers" (cost=0.41..2,407.10 rows=50,004 width=37) (actual time=0.077..32.382 rows=50,004 loops=1)

  • Heap Fetches: 5
9.          

SubPlan (forHash Right Join)

10. 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
11. 44.934 44.934 ↓ 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=44.934..44.934 rows=0 loops=1)

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