Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id ED0921337B81 for ; Fri, 15 Apr 2011 14:54:40 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 71201-07 for ; Fri, 15 Apr 2011 17:54:33 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 68B5F1336FA7 for ; Fri, 15 Apr 2011 14:54:33 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 15 Apr 2011 12:54:20 -0500 Message-Id: <4DA83FFA020000250003C89D@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Fri, 15 Apr 2011 12:54:18 -0500 From: "Kevin Grittner" To: "Mark Williams" , Subject: Re: Bad Query Plan with Range Query References: <4DA87DAC.30006@jivesoftware.com> In-Reply-To: <4DA87DAC.30006@jivesoftware.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/224 X-Sequence-Number: 43286 Mark Williams wrote: > explain SELECT min(messageID) FROM Message > WHERE modificationDate >= 1302627793988; > For some reason it is deciding to scan the primary key column of > the table. This results in scanning the entire table No, it scans until it finds the first row where modificationDate >= 1302627793988, at which point the scan is done because it's doing an ascending scan on what you want the min() of. You might have a clue that the first such row will be ten million rows into the scan, but the optimizer doesn't know that. It's assuming that rows which meet that condition are scattered randomly through the primary key range. It thinks that it will, on average, need to scan 1249 rows to find a match. The patch Ken referenced causes the alternative to be assigned a more accurate (and lower) cost, which tips the scales in favor of that plan -- at least for the case you've tried; but this seems to me to be another case related to the correlation of values. It's a new and different form of it, but it seems at least somewhat related. It might be a good example for those working on multi-column statistics to keep in mind. -Kevin