Re: How to boost performance of queries containing pattern matching characters - Mailing list pgsql-performance

On 14/02/11 07:38, Artur Zając wrote:
> 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
...

Looks like you've almost re-invented the trigram module:
   http://www.postgresql.org/docs/9.0/static/pgtrgm.html

--
   Richard Huxton
   Archonet Ltd