On 5/9/11 9:59 , Kevin Grittner wrote:
You don't need to do that; you can specify an opclass for the index
to tell it that you don't want to order by the normal collation, but
rather in a way which will allow the index to be useful for pattern
matching:
http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html
-Kevin
Hi,
Thanks for the explanation. Works brilliantly!
Best regards,
Marcus
For future googlers:
http://www.postgresonline.com/journal/archives/78-Why-is-my-index-not-being-used.html
drop index bond_item_common_x7;
CREATE INDEX bond_item_common_x7 ON bond_item_common USING
btree(lower(original_filename) varchar_pattern_ops);
bond90=> explain analyze
select pic2.objectid
from bond_item_common pic2
where
lower(pic2.original_filename) like 'this is a test%' ;
QUERY PLAN
--------------------------------------------------------------...
Bitmap Heap Scan on bond_item_common pic2 (cost=705.84..82746.05
rows=23870 width=4) (actual time=0.015..0.015 rows=0 loops=1)
Filter: (lower((original_filename)::text) ~~ 'this is a test%'::text)
-> Bitmap Index Scan on bond_item_common_x7 (cost=0.00..699.87
rows=23870 width=0) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((lower((original_filename)::text) ~>=~ 'this is a
test'::text) AND (lower((original_filename)::text) ~<~ 'this is a
tesu'::text))
Total runtime: 0.033 ms
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
