Re: getting ILIKE or ~* to use indexes.... - Mailing list pgsql-sql

Rajesh,

> I want my query to use indexes for company name searches but its not
> happening unless
> is use '=' which does not server the purpose.
> 
> eg
> 
> tradein_clients=# explain SELECT co_name  FROM unified_data  where
> co_name  ilike '%rajesh%' ;

Well, for an *anchored* case-insensitive search, you can create an
index on lower(field_name) to use an index.

CREATE INDEX idx_uni_co_name ON unifed_data(lower(co_name));
SELECT co_name  FROM unified_data  where
lower(co_name) LIKE (lower('rajesh') || '%') ;

And that will use the index.

However, what you are doing is an *unanchored* text search, meaning
that you are searching for 'rajesh' anywhere in the field.  No standard
index can help you with that.

Instead, you should look into Full Text Search tools.  There's a simple
one in /contrib in the Postgresql source, and an more robust one
available from the OpenFTS project.

-Josh Berkus