How to boost performance of queries containing pattern matching characters

From: "Gnanakumar" <gnanam(at)zoniac(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: How to boost performance of queries containing pattern matching characters
Date: 2011-02-14 06:59:04
Message-ID: 004601cbcc14$acfe99d0$06fbcd70$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)domain(dot)com'.

MYTABLE currently contains 2 million records and grows consistently.

Regards,
Gnanam

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2011-02-14 07:01:37 Re: Why we don't want hints
Previous Message Mark Rostron 2011-02-14 04:40:20 Re: comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan