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
Message-ID: <004601cbcc14$acfe99d0$06fbcd70$@com> (raw)
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
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: gnanam@zoniac.com
Subject: Re: How to boost performance of queries containing pattern matching characters
In-Reply-To: <004601cbcc14$acfe99d0$06fbcd70$@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