Message-ID: <4DC85BCA.1090707@engene.se>
Date: Mon, 09 May 2011 23:25:30 +0200
From: Marcus Engene <mengpg2@engene.se>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.16) Gecko/20101125 Thunderbird/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: wildcard makes seq scan on prod db but not in		 test
References: <4DC82D28.7020105@engene.se>
	<4DC7F2B3020000250003D424@gw.wicourts.gov>
	<4DC83C44.5070206@engene.se>
	<4DC80149020000250003D42E@gw.wicourts.gov>
In-Reply-To: <4DC80149020000250003D42E@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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