Re: wildcard makes seq scan on prod db but not in test

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