Thread: getting ILIKE or ~* to use indexes....

Hi folks,

can anyone tell me or point me to the right thread.

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%' ;
NOTICE:  QUERY PLAN:
Seq Scan on unified_data  (cost=0.00..19293.00 rows=1 width=25)
EXPLAIN
tradein_clients=# explain SELECT co_name  FROM unified_data  where co_name  = 'rajesh' ;
NOTICE:  QUERY PLAN:
Index Scan using unified_data_co_name_key on unified_data  (cost=0.00..6.26 rows=1 width=25)
EXPLAIN
tradein_clients=#


Regards
mallah.


--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

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

Thanks Josh

I normally use tsearch for full text search i will probably use that
or may be this what u suggested.

regds
mallah.


> 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



-----------------------------------------
Get your free web based email at trade-india.com.  "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/