Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AD8309F928A for ; Thu, 6 Sep 2007 21:06:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41172-10 for ; Thu, 6 Sep 2007 21:06:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id F3ED69F925E for ; Thu, 6 Sep 2007 21:06:38 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 0B4CF3141E; Fri, 7 Sep 2007 02:06:36 +0200 (MET DST) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: How planner decides left-anchored LIKE can use index Date: Thu, 6 Sep 2007 20:06:44 -0400 Organization: Hub.Org Networking Services Lines: 14 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6000.16480 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16480 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL=-0.000, BAYES_50=0.001, STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 200709/107 X-Sequence-Number: 26652 Exactly when does the planner decide that a left-anchored like can use the index? I have replaced a WHEN lower(last_name) = 'smith' with WHEN lower(last_name) like 'smith%' There is an index on lower(last_name). I have seen the planner convert the LIKE to lower(last_name) >= 'smith' and lower(last_name) < 'smiti' on 8.2.4 systems, but a slow sequence scan and filter on 8.1.9 - is this related to the version difference (8.1.9 vs 8.2.4) or is this related to something like operators/classes that have been installed? Carlo