From: =?iso-8859-2?Q?Artur_Zaj=B1c?= <azajac@ang.com.pl>
To: <gnanam@zoniac.com>,
	"'Richard Huxton'" <dev@archonet.com>
Cc: <pgsql-performance@postgresql.org>
References: <004601cbcc14$acfe99d0$06fbcd70$@com>
	<4D58D752.2000100@archonet.com>
	<004701cbcc18$c54c8980$4fe59c80$@com>
In-Reply-To: <004701cbcc18$c54c8980$4fe59c80$@com>
Subject: Re: How to boost performance of queries containing pattern matching
	characters
Date: Mon, 14 Feb 2011 08:38:48 +0100
Message-ID: <003801cbcc1a$37d2f7e0$a778e7a0$@ang.com.pl>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit
Thread-Index: AQJMtgQYv5PXiwGLv3AG4jOO/Eh+PwFEXM31AMeiMN6S7mhmAA==
Content-Language: pl

>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.

I had almost the same problem.
To resolve it, I created my own text search parser (myftscfg) which divides
text in column into three letters parts, for example:

someemail@domain.com is divided to som, ome,mee,eem,ema,mai,ail,il@,
l@d,@do,dom,oma,mai,ain,in.,n.c,.co,com

There should be also index on email column:

CREATE INDEX "email _fts" on mytable using gin
(to_tsvector('myftscfg'::regconfig, email))

Every query like email ilike '%domain.com%' should be rewrited to:

WHERE
to_tsvector('myftscfg',email) @@ to_tsquery('dom') AND
to_tsvector('myftscfg',email) @@ to_tsquery('oma') AND
to_tsvector('myftscfg',email) @@ to_tsquery('mai') AND
to_tsvector('myftscfg',email) @@ to_tsquery('ain') AND
to_tsvector('myftscfg',email) @@ to_tsquery('in.') AND
to_tsvector('myftscfg',email) @@ to_tsquery('n.c') AND
to_tsvector('myftscfg',email) @@ to_tsquery('.co') AND
to_tsvector('myftscfg',email) @@ to_tsquery('com') AND email ILIKE
'%domain.com%';

Index is reducing number of records and clause email ILIKE '%domain.com%' is
selecting only valid records.

I didn't found better solution.

-------------------------------------------
Artur Zajac