Message-ID: <4D58D752.2000100@archonet.com>
Date: Mon, 14 Feb 2011 07:18:42 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.15pre) Gecko/20110202 Lightning/1.0b2 Shredder/3.1.9pre
MIME-Version: 1.0
To: gnanam@zoniac.com
CC: pgsql-performance@postgresql.org
Subject: Re: How to boost performance of queries containing pattern
	matching characters
References: <004601cbcc14$acfe99d0$06fbcd70$@com>
In-Reply-To: <004601cbcc14$acfe99d0$06fbcd70$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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