X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 918BA9DC867 for ; Wed, 1 Mar 2006 07:23:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59848-03 for ; Wed, 1 Mar 2006 07:23:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.enyo.de (mail.enyo.de [212.9.189.167]) by postgresql.org (Postfix) with ESMTP id 6740C9DC840 for ; Wed, 1 Mar 2006 07:23:13 -0400 (AST) Received: from deneb.vpn.enyo.de ([212.9.189.177] helo=deneb.enyo.de) by mail.enyo.de with esmtp id 1FEPQI-0001hw-RR; Wed, 01 Mar 2006 12:23:14 +0100 Received: from fw by deneb.enyo.de with local (Exim 4.60) (envelope-from ) id 1FELca-0000dn-W6; Wed, 01 Mar 2006 08:19:41 +0100 From: Florian Weimer To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: Index to support LIKE '%suffix' queries References: <87mzgfh69m.fsf@mid.deneb.enyo.de> <11673.1140888147@sss.pgh.pa.us> <877j7jfhqs.fsf@mid.deneb.enyo.de> <20060225181346.GA6092@surnet.cl> Date: Wed, 01 Mar 2006 08:19:40 +0100 In-Reply-To: <20060225181346.GA6092@surnet.cl> (Alvaro Herrera's message of "Sat, 25 Feb 2006 15:13:46 -0300") Message-ID: <874q2ik4xf.fsf@mid.deneb.enyo.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200603/4 X-Sequence-Number: 24350 * Alvaro Herrera: >> > Florian Weimer writes: >> >> Is it possible to create an index to support queries of the form >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> >> 'prefix%', which I also need)? > > It is possible to create a functional index on the reverse of the > string. Okay. Is there a predefined reverse function? I couldn't find one and I'm wondering if I just missed it. > Whether or not this beats tsearch2 is something you should investigate ... It's also possible that for this type of query, sequential scans are good enough. I forgot that they are quite fast.