Re: ilike not using index.

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

_________________________________________________________________
New, Used, Demo, Dealer or Private? Find it at CarPoint.com.au
http://clk.atdmt.com/NMN/go/206222968/direct/01/

Darryl Pye <darrylpye(at)hotmail(dot)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 index doesn'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

On Mon, Jun 21, 2010 at 03:36:07PM -0400, Tom Lane wrote:
> Darryl Pye <darrylpye(at)hotmail(dot)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 index doesn'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

Kenneth Marshall <ktm(at)rice(dot)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