Обсуждение: 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/