Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 67AF413364DE for ; Mon, 14 Feb 2011 04:06:47 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 09732-05 for ; Mon, 14 Feb 2011 08:06:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from rs73.luxsci.com (rs73.luxsci.com [64.49.254.135]) by mail.postgresql.org (Postfix) with ESMTP id E41401337B34 for ; Mon, 14 Feb 2011 04:06:39 -0400 (AST) Received: from GNANAM ([180.151.61.10]) (authenticated bits=0) by rs73.luxsci.com (8.13.8/8.13.8) with ESMTP id p1E85W8e011720; Mon, 14 Feb 2011 02:05:34 -0600 Reply-To: From: "Gnanakumar" To: "'Richard Huxton'" Cc: References: <004601cbcc14$acfe99d0$06fbcd70$@com> <4D58D752.2000100@archonet.com> <004701cbcc18$c54c8980$4fe59c80$@com> <4D58DC35.8010007@archonet.com> <004801cbcc1b$3f1d4fe0$bd57efa0$@com> <4D58E048.9020506@archonet.com> In-Reply-To: <4D58E048.9020506@archonet.com> Subject: Re: How to boost performance of queries containing pattern matching characters Date: Mon, 14 Feb 2011 13:32:14 +0530 Organization: Zoniac Software Private Limited Message-ID: <004c01cbcc1d$7fa04980$7ee0dc80$@com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcvMHMI7B2jVklAhT3SbWHqU+RG4XQAAA+hA Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201102/378 X-Sequence-Number: 42516 > The closest you can do is something like Artur's option (or the pg_trgm > module - handy since you are looking at 3-chars and up) to select likely > matches combined with a separate search on '%domain.com%' to confirm > that fact. Thanks for your suggestion. Our production server is currently running PostgreSQL v8.2.3. I think pg_trgm contrib module is not available for 8.2 series. Also, I read about WildSpeed - fast wildcard search for LIKE operator. What is your opinion on that? http://www.sai.msu.su/~megera/wiki/wildspeed