Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7C9B19FA30E for ; Thu, 6 Sep 2007 21:28: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 43168-10 for ; Thu, 6 Sep 2007 21:28:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 742B29FA17B for ; Thu, 6 Sep 2007 21:28:18 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l870SGAw006717; Thu, 6 Sep 2007 20:28:16 -0400 (EDT) To: "Carlo Stonebanks" cc: pgsql-performance@postgresql.org Subject: Re: How planner decides left-anchored LIKE can use index In-reply-to: References: Comments: In-reply-to "Carlo Stonebanks" message dated "Thu, 06 Sep 2007 20:06:44 -0400" Date: Thu, 06 Sep 2007 20:28:16 -0400 Message-ID: <6716.1189124896@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/108 X-Sequence-Number: 26653 "Carlo Stonebanks" writes: > 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? Most likely you used C locale for the 8.2.4 installation and some other locale for the other one. In non-C locale you can still get the optimization if you create an index using the text_pattern_ops opclass ... but beware that this index is useless for the normal locale-aware operators. regards, tom lane