Postgres Pro
Downloads
Home   >   mailing lists

Thread: ilike not using index.

ilike not using index.

From
Darryl Pye
Date:

Hi,
Index was working correctly in 8.4,I have upgraded to  version 9 as I require some of the new features and now the index doesn't work.
Probably something stupid I have forgotten to do when upgrading.

I have an index on a large  > 1million  records table.
CREATE INDEX "t1~index"  ON coop.t1 USING btree (full_name text_pattern_ops);

For any query the result set will always be small and I am also Limiting to  top 20 records.

When I use a  ilike   eg.
select * from t1 where full_name ilike 'test%' limit 20;
it always does a full scan and takes forever.

If I use like
select * from t1 where full_name like 'test%' limit 20;
it uses the index.


Was working in 8.4.

I have tried recreating the index as well as all theses steps,
http://www.postgresonline.com/journal/index.php?/archives/78-Why-is-my-index-not-being-used.html

but still cannot get it to work.

Regards,
Darryl


Find it at CarPoint.com.au New, Used, Demo, Dealer or Private?

Re: ilike not using index.

From
Tom Lane
Date:
Darryl Pye <darrylpye@hotmail.com> writes:
> Index was working correctly in 8.4,I have upgraded to  version 9 as I require some of the new features and now the
indexdoesn't work. 

No version of Postgres has ever been able to use an index for ILIKE.

You might consider creating an index on lower(full_name) and then
querying WHERE lower(full_name) LIKE whatever.

            regards, tom lane

Re: ilike not using index.

From
Kenneth Marshall
Date:
On Mon, Jun 21, 2010 at 03:36:07PM -0400, Tom Lane wrote:
> Darryl Pye <darrylpye@hotmail.com> writes:
> > Index was working correctly in 8.4,I have upgraded to  version 9 as I require some of the new features and now the
indexdoesn't work. 
>
> No version of Postgres has ever been able to use an index for ILIKE.
>
> You might consider creating an index on lower(full_name) and then
> querying WHERE lower(full_name) LIKE whatever.
>
>             regards, tom lane
>

Would citext support this?

Regards,
Ken

Re: ilike not using index.

From
Tom Lane
Date:
Kenneth Marshall <ktm@rice.edu> writes:
> On Mon, Jun 21, 2010 at 03:36:07PM -0400, Tom Lane wrote:
>> You might consider creating an index on lower(full_name) and then
>> querying WHERE lower(full_name) LIKE whatever.

> Would citext support this?

No, unfortunately.  The index optimization for LIKE is hard-wired into
the planner, so it only supports built-in data types.  Someday it'd be
nice to have a way for add-on modules to insert that sort of
optimization.

            regards, tom lane