Message-ID: <4D58DC35.8010007@archonet.com>
Date: Mon, 14 Feb 2011 07:39:33 +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>
	<4D58D752.2000100@archonet.com>
	<004701cbcc18$c54c8980$4fe59c80$@com>
In-Reply-To: <004701cbcc18$c54c8980$4fe59c80$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 14/02/11 07:28, Gnanakumar wrote:
>> Is that really what you are after? Or, did you just want to match:
>>     user1@domain.com
>>     user2@sub.domain.com
>
> I understand that because I've (%) at the beginning and end, it's going to
> match unrelated domains, etc., which as you said rightly, it is
> wide-ranging.  But my point here is that how can I improve performance of
> the queries containing pattern matching characters.

If you really need to match all those options, you can't use an index. A 
substring-matching index would need to have multiple entries per 
character per value (since it doesn't know what you will search for). 
The index-size becomes unmanageable very quickly.

That's why I asked what you really wanted to match.

So, I'll ask again: do you really want to match all of those options?

-- 
   Richard Huxton
   Archonet Ltd