From: Richard Huxton <dev@archonet.com>
To: gnanam@zoniac.com
Cc: pgsql-performance@postgresql.org
Subject: Re: How to boost performance of queries containing pattern matching characters
Date: Mon, 14 Feb 2011 07:18:42 +0000
Message-ID: <4D58D752.2000100@archonet.com> (raw)
In-Reply-To: <004601cbcc14$acfe99d0$06fbcd70$@com>
References: <004601cbcc14$acfe99d0$06fbcd70$@com>
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@domain.com
user2@sub.domain.com
user3@domain.com.au
user4@unrelated-domain.com
user5@unrelated-domain.com.au
user3@sub.domain.com.au
user4@sub.unrelated-domain.com
user5@sub.unrelated-domain.com.au
user6@sub.unrelated-domain.completely-wrong.com
Is that really what you are after? Or, did you just want to match:
user1@domain.com
user2@sub.domain.com
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, gnanam@zoniac.com
Subject: Re: How to boost performance of queries containing pattern matching characters
In-Reply-To: <4D58D752.2000100@archonet.com>
* 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