Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 616A49FB520 for ; Thu, 4 Oct 2007 18:05:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 29439-01-4 for ; Thu, 4 Oct 2007 18:04:58 -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 334F19FB4D4 for ; Thu, 4 Oct 2007 18:04:57 -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 l94L4r9x021102; Thu, 4 Oct 2007 17:04:53 -0400 (EDT) To: Ben cc: Simon Riggs , pgsql-performance@postgresql.org Subject: Re: quickly getting the top N rows In-reply-to: References: <20071004141411.9509fe66.wmoran@collaborativefusion.com> <3785.1191523930@sss.pgh.pa.us> <1191529385.4223.231.camel@ebony.site> Comments: In-reply-to Ben message dated "Thu, 04 Oct 2007 13:34:22 -0700" Date: Thu, 04 Oct 2007 17:04:53 -0400 Message-ID: <21101.1191531893@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/49 X-Sequence-Number: 27071 Ben writes: > On Thu, 4 Oct 2007, Simon Riggs wrote: > I thought that might explain it, but then I'm surprised that it can still > use an index when the first two columns of the index aren't in the query. > Wouldn't that mean that it might have to walk the entire index to find > matching rows? > ....unless it's smart enough to realize that the first two columns will > match everything. Which would be cool. There's some limited smarts in there about deciding that leading columns of an index don't matter to the sort ordering if they're constrained to just one value by the query. But it doesn't catch the case you need, which is that columns of an ORDER BY request are no-ops when they're constrained to just one value. That whole area has been rewritten for 8.3 and I believe it will handle this case. No time to try it right now though. regards, tom lane