ilike not using index.

From: Darryl Pye <darrylpye(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: ilike not using index.
Date: 2010-06-15 06:53:35
Message-ID: COL109-W26A1C642DB7F8E021EDC88B3DD0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-novice

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,

but still cannot get it to work.


New, Used, Demo, Dealer or Private? Find it at


Browse pgsql-novice by date

  From Date Subject
Next Message Jasen Betts 2010-06-15 09:14:57 Re: (not so?) silly question
Previous Message Andreas Kretschmer 2010-06-15 04:55:45 Re: (not so?) silly question