From: Richard Huxton <dev@archonet.com>
To: Artur Zając <azajac@ang.com.pl>
Cc: gnanam@zoniac.com
Cc: pgsql-performance@postgresql.org
Subject: Re: How to boost performance of queries containing pattern matching characters
Date: Mon, 14 Feb 2011 07:49:54 +0000
Message-ID: <4D58DEA2.3040202@archonet.com> (raw)
In-Reply-To: <003801cbcc1a$37d2f7e0$a778e7a0$@ang.com.pl>
References: <004601cbcc14$acfe99d0$06fbcd70$@com>
<4D58D752.2000100@archonet.com>
<004701cbcc18$c54c8980$4fe59c80$@com>
<003801cbcc1a$37d2f7e0$a778e7a0$@ang.com.pl>
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
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: dev@archonet.com, azajac@ang.com.pl, gnanam@zoniac.com
Subject: Re: How to boost performance of queries containing pattern matching characters
In-Reply-To: <4D58DEA2.3040202@archonet.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