Re: quickly getting the top N rows

On Thu, 4 Oct 2007, Simon Riggs wrote:

> In the first query, Postgres cannot use the index because the sort order
> of the index does not match the sort order of the query. When you change
> the sort order of the query so that it matches that of the index, then
> the index is used.
>
> If you define your index on (logtime, logkey, clientkey, premiseskey)
> rather than on (clientkey, premiseskey, logtime, logkey) you will have a
> fast query. Yes, the column order matters.

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.