Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E882E9FB78B for ; Fri, 25 May 2007 14:30:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 78479-02 for ; Fri, 25 May 2007 14:30:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from h07.llord.com (unknown [195.140.142.194]) by postgresql.org (Postfix) with ESMTP id B7E009FB6FA for ; Fri, 25 May 2007 14:30:06 -0300 (ADT) Received: from par69-8-88-161-102-87.fbx.proxad.net ([88.161.102.87] helo=apollo13) by h07.llord.com with esmtpa (Exim 4.66) (envelope-from ) id 1Hrdae-0002PG-R7; Fri, 25 May 2007 19:28:37 +0200 Date: Fri, 25 May 2007 19:31:16 +0200 To: "Richard Huxton" Subject: Re: LIKE search and performance From: PFC Cc: mark@mark.mielke.cc, "Mark Lewis" , "James Mansion" , "Magnus Hagander" , "Alexander Staubo" , Andy , pgsql-performance@postgresql.org Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom> <88daf38c0705230905u1a981102k691b47bd36011a80@mail.gmail.com> <4655DE75.4080506@mansionfamily.plus.com> <4655E64B.5050503@hagander.net> <4655FB8A.1040707@mansionfamily.plus.com> <1180040560.31471.262.camel@archimedes> <20070524215450.GA3858@mark.mielke.cc> <46569AA5.10205@archonet.com> <20070525141630.GA12162@mark.mielke.cc> <4657023A.20209@archonet.com> <46571825.30707@archonet.com> Content-Transfer-Encoding: 7bit Message-ID: In-Reply-To: <46571825.30707@archonet.com> User-Agent: Opera Mail/9.10 (Linux) X-PopBeforeSMTPSenders: junk@peufeu.com, lists@peufeu.com, peufeu@peufeu.com, pfcaillaud@peufeu.com X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - h07.llord.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - peufeu.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.261 tagged_above=0 required=5 tests=AWL=-0.515, BAYES_50=0.001, RCVD_IN_NJABL_SPAM=2.775 X-Spam-Level: ** X-Archive-Number: 200705/526 X-Sequence-Number: 24916 > None of which address the question of what plan PG should produce for: > SELECT * FROM bigtable WHERE foo LIKE 's%' Ah, this one already uses the btree since the '%' is at the end. My point is that a search like this will yield too many results to be useful to the user anyway, so optimizing its performance is a kind of red herring.