From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: getting ILIKE or  ~*  to use indexes....
To: "Rajesh Kumar Mallah." <mallah@trade-india.com>, pgsql-sql@postgresql.org
Date: Sat, 10 Aug 2002 11:10:16 -0700
Message-ID: <web-1607387@davinci.ethosmedia.com>
In-Reply-To: <200208101143.54041.mallah@trade-india.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

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