Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5E08E1337B51 for ; Mon, 14 Feb 2011 03:50:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 62878-06 for ; Mon, 14 Feb 2011 07:49:55 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by mail.postgresql.org (Postfix) with ESMTP id E1C7D1337990 for ; Mon, 14 Feb 2011 03:49:54 -0400 (AST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by mail.archonet.com (Postfix) with ESMTP id 3B2547C137; Mon, 14 Feb 2011 07:49:54 +0000 (GMT) Message-ID: <4D58DEA2.3040202@archonet.com> Date: Mon, 14 Feb 2011 07:49:54 +0000 From: Richard Huxton User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.15pre) Gecko/20110202 Lightning/1.0b2 Shredder/3.1.9pre MIME-Version: 1.0 To: =?ISO-8859-2?Q?Artur_Zaj=B1c?= CC: gnanam@zoniac.com, pgsql-performance@postgresql.org Subject: Re: How to boost performance of queries containing pattern matching characters References: <004601cbcc14$acfe99d0$06fbcd70$@com> <4D58D752.2000100@archonet.com> <004701cbcc18$c54c8980$4fe59c80$@com> <003801cbcc1a$37d2f7e0$a778e7a0$@ang.com.pl> In-Reply-To: <003801cbcc1a$37d2f7e0$a778e7a0$@ang.com.pl> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.918 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201102/374 X-Sequence-Number: 42512 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