From: Shaun Thomas <sthomas@peak6.com>
To: gnanam@zoniac.com <gnanam@zoniac.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: How to boost performance of queries containing pattern matching characters
Date: Mon, 14 Feb 2011 07:55:36 -0600
Message-ID: <4D593458.1020004@peak6.com> (raw)
In-Reply-To: <004601cbcc14$acfe99d0$06fbcd70$@com>
References: <004601cbcc14$acfe99d0$06fbcd70$@com>
On 02/14/2011 12:59 AM, Gnanakumar wrote:
> QUERY: DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'
> EMAIL column is VARCHAR(256).
Honestly? You'd be better off normalizing this column and maybe hiding
that fact in a view if your app requires email as a single column. Split
it like this:
So user@gmail.com becomes:
email_acct (user)
email_domain (gmail)
email_tld (com)
This would let you drop the first % on your like match and then
traditional indexes would work just fine. You could also differentiate
between domains with different TLDs without using wildcards, which is
always faster.
I might ask why you are checking email for wildcards after the TLD in
the first place. Is it really so common you are trying to match .com,
.com.au, .com.bar.baz.edu, or whatever? At the very least, splitting the
account from the domain+tld would be beneficial, as it would remove the
necessity of the first wildcard, which is really what's hurting you.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
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: sthomas@peak6.com, gnanam@zoniac.com
Subject: Re: How to boost performance of queries containing pattern matching characters
In-Reply-To: <4D593458.1020004@peak6.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