explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gGT6

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 2,403.365 ↑ 1.2 81 1

Limit (cost=41,318.71..41,318.96 rows=100 width=575) (actual time=2,403.354..2,403.365 rows=81 loops=1)

2. 1.361 2,403.357 ↑ 309.3 81 1

Sort (cost=41,318.71..41,381.35 rows=25,055 width=575) (actual time=2,403.352..2,403.357 rows=81 loops=1)

  • Sort Key: "Banks"."Name", "Customers"."FullName
  • Sort Method: quicksort Memory: 99kB
3. 0.364 2,401.996 ↑ 309.3 81 1

Nested Loop (cost=30,805.47..40,361.13 rows=25,055 width=575) (actual time=262.140..2,401.996 rows=81 loops=1)

4. 0.029 0.029 ↑ 1.0 1 1

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

  • Index Cond: ("BankId" = 1)
5. 1,437.648 2,401.603 ↑ 309.3 81 1

Hash Right Join (cost=30,805.34..40,102.43 rows=25,055 width=565) (actual time=262.105..2,401.603 rows=81 loops=1)

  • Hash Cond: ("CustomerAddresses"."CustomerId" = "Customers"."CustomerId")
  • Filter: ((to_tsvector('simple'::regconfig, f_concat_ws(' '::text, VARIADIC ARRAY[("Customers"."CustomerNumber")::text, ("Customers"."TaxId")::text, ("Customers"."FullName")::text])) @@ '''michael'''::tsquery) 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: 49923
6. 69.860 80.049 ↓ 1.0 50,004 1

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

  • Recheck Cond: ("AddressIndex" = 0)
  • Heap Blocks: exact=2245
7. 10.189 10.189 ↓ 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=10.189..10.189 rows=50,004 loops=1)

8. 61.659 234.907 ↑ 1.0 50,004 1

Hash (cost=24,683.49..24,683.49 rows=50,004 width=565) (actual time=234.907..234.907 rows=50,004 loops=1)

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

Index Scan using ix_customers_bankid on "Customers" (cost=0.41..24,683.49 rows=50,004 width=565) (actual time=0.299..173.248 rows=50,004 loops=1)

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

SubPlan (forHash Right Join)

11. 399.384 648.999 ↓ 0.0 0 49,923

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

  • Recheck Cond: ("CustomerId" = "Customers"."CustomerId")
  • Filter: (("AccountNumber")::text ~~ '%michael%'::text)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=171135
12. 249.615 249.615 ↑ 1.3 3 49,923

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

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

Seq Scan on "CustomerRelationships" "CustomerRelationships_1" (cost=10,000,000,000.00..10,000,010,881.11 rows=12 width=4) (never executed)

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