Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
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


pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group