X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CE5579FA5C3 for ; Mon, 3 Jul 2006 04:34:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 50969-04 for ; Mon, 3 Jul 2006 04:33:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 168559FA2F3 for ; Mon, 3 Jul 2006 04:33:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from relay.icomedias.com (relay.icomedias.com [62.99.232.66]) by svr4.postgresql.org (Postfix) with ESMTP id A6E755AF04A for ; Mon, 3 Jul 2006 07:33:57 +0000 (GMT) Received: from loki.icomedias.com ([10.192.17.128]) by relay.icomedias.com (8.13.6/8.13.6) with ESMTP id k637XrnR026808; Mon, 3 Jul 2006 09:33:53 +0200 From: Mario Weilguni To: pgsql-performance@postgresql.org, genekhart@gmail.com Subject: Re: optimizing LIKE '%2345' queries Date: Mon, 3 Jul 2006 09:33:53 +0200 User-Agent: KMail/1.9.3 References: <430d92a20607021450i1d228350j292d68e987ed58de@mail.gmail.com> In-Reply-To: <430d92a20607021450i1d228350j292d68e987ed58de@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200607030933.53373.mweilguni@sime.com> X-Scanned-By: MIMEDefang 2.54 on 10.192.64.200 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/6 X-Sequence-Number: 19866 Am Sonntag, 2. Juli 2006 23:50 schrieb Gene: > Is there any way to create a reverse index on string columns so that > queries of the form: > > where column like '%2345'; > > can use an index and perform as fast as searching with like '2345%'? > > Is the only way to create a reverse function and create an index using > the reverse function and modify queries to use: > > where reverse(column) like reverse('%2345') ? > > thanks create a trigger that computes this at insert/update time, index this fix, and rewrite the query this way: where inverted_column like '5432%';