From: Ben <bench@silentmedia.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
Date: Thu, 4 Oct 2007 13:34:22 -0700 (PDT)
Message-ID: <Pine.LNX.4.64.0710041330150.30864@localhost.localdomain> (raw)
In-Reply-To: <1191529385.4223.231.camel@ebony.site>
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>
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.
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: bench@silentmedia.com, simon@2ndquadrant.com, tgl@sss.pgh.pa.us
Subject: Re: quickly getting the top N rows
In-Reply-To: <Pine.LNX.4.64.0710041330150.30864@localhost.localdomain>
* 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