From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ben <bench@silentmedia.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
Date: Thu, 04 Oct 2007 17:04:53 -0400
Message-ID: <21101.1191531893@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0710041330150.30864@localhost.localdomain>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
<20071004141411.9509fe66.wmoran@collaborativefusion.com>
<Pine.LNX.4.64.0710041126010.30864@localhost.localdomain>
<3785.1191523930@sss.pgh.pa.us>
<Pine.LNX.4.64.0710041229080.30864@localhost.localdomain>
<1191529385.4223.231.camel@ebony.site>
<Pine.LNX.4.64.0710041330150.30864@localhost.localdomain>
Ben <bench@silentmedia.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, bench@silentmedia.com, simon@2ndquadrant.com
Subject: Re: quickly getting the top N rows
In-Reply-To: <21101.1191531893@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox