Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: <pgsql-performance@postgresql.org>
Subject: How to boost performance of queries containing pattern matching
	characters
Date: Mon, 14 Feb 2011 12:29:04 +0530
Organization: Zoniac Software Private Limited
Message-ID: <004601cbcc14$acfe99d0$06fbcd70$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcvMFKousvyANEy/Thiuh0xtm7zlIA==
Content-Language: en-us

Hi,

How can we boost performance of queries containing pattern matching
characters?  In my case, we're using a percent sign (%) that matches any
string of zero or more characters.

QUERY:  DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'

EMAIL column is VARCHAR(256).

As it is clear from the above query, email is matched "partially and
case-insensitively", which my application requirement demands.  

In case, if it were a full match, I could easily define a functional INDEX
on EMAIL column (lower(EMAIL)) and I could rewrite my DELETE where criteria
like lower(EMAIL) = 'someemail@domain.com'.

MYTABLE currently contains 2 million records and grows consistently.

Regards,
Gnanam