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?