From: Marcus Engene <mengpg2@engene.se>
To: pgsql-performance@postgresql.org
Subject: Re: wildcard makes seq scan on prod db but not in test
Date: Mon, 09 May 2011 23:25:30 +0200
Message-ID: <4DC85BCA.1090707@engene.se> (raw)
In-Reply-To: <4DC80149020000250003D42E@gw.wicourts.gov>
References: <4DC82D28.7020105@engene.se>
<4DC7F2B3020000250003D424@gw.wicourts.gov>
<4DC83C44.5070206@engene.se>
<4DC80149020000250003D42E@gw.wicourts.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mengpg2@engene.se
Subject: Re: wildcard makes seq scan on prod db but not in test
In-Reply-To: <4DC85BCA.1090707@engene.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox