pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting ILIKE or ~* to use indexes....
3+ messages / 3 participants
[nested] [flat]
* getting ILIKE or ~* to use indexes....
@ 2002-08-10 06:13 Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-08-10 18:10 ` Re: getting ILIKE or ~* to use indexes.... Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-08-10 06:13 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: getting ILIKE or ~* to use indexes....
2002-08-10 06:13 getting ILIKE or ~* to use indexes.... Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-08-10 18:10 ` Josh Berkus <josh@agliodbs.com>
2002-08-10 21:14 ` Re: getting ILIKE or ~* to use indexes.... mallah@trade-india.com
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2002-08-10 18:10 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: getting ILIKE or ~* to use indexes....
2002-08-10 06:13 getting ILIKE or ~* to use indexes.... Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-08-10 18:10 ` Re: getting ILIKE or ~* to use indexes.... Josh Berkus <josh@agliodbs.com>
@ 2002-08-10 21:14 ` mallah@trade-india.com
0 siblings, 0 replies; 3+ messages in thread
From: mallah@trade-india.com @ 2002-08-10 21:14 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: mallah@trade-india.com; pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-08-10 21:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-10 06:13 getting ILIKE or ~* to use indexes.... Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-08-10 18:10 ` Josh Berkus <josh@agliodbs.com>
2002-08-10 21:14 ` mallah@trade-india.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox