| From: | Richard Huxton <dev(at)archonet(dot)com> |
|---|---|
| To: | gnanam(at)zoniac(dot)com |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: How to boost performance of queries containing pattern matching characters |
| Date: | 2011-02-14 07:18:42 |
| Message-ID: | 4D58D752.2000100@archonet.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On 14/02/11 06:59, Gnanakumar wrote:
>
> How can we boost performance of queries containing pattern matching
> characters?
> QUERY: DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'
> As it is clear from the above query, email is matched "partially and
> case-insensitively", which my application requirement demands.
Well, for that exact pattern you're not going to find an index that's
much help. Do you really need something so wide-ranging though? The
above will match all of the following:
user1(at)domain(dot)com
user2(at)sub(dot)domain(dot)com
user3(at)domain(dot)com(dot)au
user4(at)unrelated-domain(dot)com
user5(at)unrelated-domain(dot)com(dot)au
user3(at)sub(dot)domain(dot)com(dot)au
user4(at)sub(dot)unrelated-domain(dot)com
user5(at)sub(dot)unrelated-domain(dot)com(dot)au
user6(at)sub(dot)unrelated-domain(dot)completely-wrong(dot)com
Is that really what you are after? Or, did you just want to match:
user1(at)domain(dot)com
user2(at)sub(dot)domain(dot)com
--
Richard Huxton
Archonet Ltd
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Gnanakumar | 2011-02-14 07:28:23 | Re: How to boost performance of queries containing pattern matching characters |
| Previous Message | Scott Marlowe | 2011-02-14 07:01:37 | Re: Why we don't want hints |