Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 04D0C1337BD1 for ; Fri, 15 Apr 2011 19:32:39 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 31013-04 for ; Fri, 15 Apr 2011 22:32:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod8og113.obsmtp.com (exprod8og113.obsmtp.com [64.18.3.26]) by mail.postgresql.org (Postfix) with ESMTP id BEEF41337BD9 for ; Fri, 15 Apr 2011 19:32:20 -0300 (ADT) Received: from mail.jiveland.com ([70.98.39.50]) (using TLSv1) by exprod8ob113.postini.com ([64.18.7.12]) with SMTP ID DSNKTajHdPDcAaqAw69qylXCB6vdQaTRpiAc@postini.com; Fri, 15 Apr 2011 15:32:21 PDT Received: from [10.61.130.191] (70.98.39.250) by mail.jiveland.com (10.61.65.25) with Microsoft SMTP Server (TLS) id 8.2.254.0; Fri, 15 Apr 2011 22:30:15 +0000 Message-ID: <4DA8C6B6.3030702@jivesoftware.com> Date: Fri, 15 Apr 2011 15:29:10 -0700 From: Mark Williams User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.14) Gecko/20110223 Thunderbird/3.1.8 MIME-Version: 1.0 CC: "pgsql-performance@postgresql.org" Subject: Re: Bad Query Plan with Range Query References: <4DA87DAC.30006@jivesoftware.com> <4DA83FFA020000250003C89D@gw.wicourts.gov> <4DA8893B.8050403@jivesoftware.com> <4DA84476020000250003C8A2@gw.wicourts.gov> In-Reply-To: <4DA84476020000250003C8A2@gw.wicourts.gov> Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.879 tagged_above=-10 required=5 tests=BAYES_00=-1.9, MISSING_HEADERS=1.021 X-Spam-Level: X-Archive-Number: 201104/227 X-Sequence-Number: 43289 Whoops, I meant this query (ordering my messageid): SELECT messageID FROM Message WHERE modificationDate>= 1302627793988 ORDER BY messageID LIMIT 1; Sometimes this gives the better plan. But not always. On 04/15/2011 11:13 AM, Kevin Grittner wrote: > Mark Williams wrote: > >> If I re-write the query like this: >> >> explain SELECT messageID FROM Message WHERE modificationDate>= >> 1302627793988 ORDER BY modificationDate LIMIT 1; > >> I also get a better plan. > > Yeah, but it's not necessarily the same value. Do you want the > minimum messageID where modificationDate>= 1302627793988 or do you > want the messageID of some row (possibly of many) with the minimum > modificationDate where modificationDate>= 1302627793988? > > Since you're asking for a logically different value with that query, > it's not surprising it uses a different plan. > > -Kevin