From: Josh Berkus <josh@agliodbs.com>
To: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: pgsql-sql@postgresql.org
Subject: Re: getting ILIKE or ~* to use indexes....
Date: Sat, 10 Aug 2002 11:10:16 -0700
Message-ID: <web-1607387@davinci.ethosmedia.com> (raw)
In-Reply-To: <200208101143.54041.mallah@trade-india.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, mallah@trade-india.com
Subject: Re: getting ILIKE or ~* to use indexes....
In-Reply-To: <web-1607387@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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