explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FO4x

Settings
# exclusive inclusive rows x rows loops node
1. 4.654 2,621.864 ↑ 290.6 259 1

Sort (cost=113,829.95..114,018.09 rows=75,256 width=549) (actual time=2,621.851..2,621.864 rows=259 loops=1)

  • Sort Key: "Banks"."Name", "Customers"."FullName
  • Sort Method: quicksort Memory: 228kB
2. 1.256 2,617.210 ↑ 290.6 259 1

Nested Loop (cost=60,261.05..89,212.39 rows=75,256 width=549) (actual time=643.318..2,617.210 rows=259 loops=1)

3. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on "Banks" (cost=0.00..7.01 rows=1 width=36) (actual time=0.021..0.023 rows=1 loops=1)

  • Filter: ("BankId" = 1)
4. 1,899.565 2,615.931 ↑ 290.6 259 1

Hash Right Join (cost=60,261.05..88,452.82 rows=75,256 width=517) (actual time=643.293..2,615.931 rows=259 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: 149775
5. 87.233 87.233 ↑ 1.0 150,030 1

Seq Scan on "CustomerAddresses" (cost=0.00..12,822.39 rows=150,031 width=106) (actual time=0.011..87.233 rows=150,030 loops=1)

  • Filter: ("AddressIndex" = 0)
  • Rows Removed by Filter: 1
6. 168.316 531.692 ↑ 1.0 150,034 1

Hash (cost=48,396.53..48,396.53 rows=150,362 width=517) (actual time=531.692..531.692 rows=150,034 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 4694kB
7. 363.376 363.376 ↑ 1.0 150,034 1

Seq Scan on "Customers" (cost=0.00..48,396.53 rows=150,362 width=517) (actual time=0.294..363.376 rows=150,034 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..8.50 rows=1 width=0) (never executed)

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

Gather (cost=1,000.42..17,512.03 rows=51 width=4) (actual time=97.361..97.441 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
11. 68.828 68.828 ↓ 0.0 0 2

Parallel Index Only Scan using idx_customerrelationships_covering2 on "CustomerRelationships" "CustomerRelationships_1" (cost=0.42..16,506.93 rows=30 width=4) (actual time=68.828..68.828 rows=0 loops=2)

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