From: Dan Langille <dan@langille.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: extending ORDER BY make query much slower
Date: Wed, 12 Mar 2003 17:48:06 -0500 (EST)
Message-ID: <20030312173223.E95239@xeon.unixathome.org> (raw)
In-Reply-To: <29647.1047506458@sss.pgh.pa.us>
References: <3E6F45D8.15284.5994716@localhost>
<29647.1047506458@sss.pgh.pa.us>
On Wed, 12 Mar 2003, Tom Lane wrote:
> "Dan Langille" <dan@langille.org> writes:
> > I've found that adding another field to the ORDER BY clause, times go
> > through the roof.
>
> Why does that surprise you?
Because I wasn't using the logic you describe below.
> The original query is using an index to
> achieve the required ordering, so it can give you the first 100 rows
> without bothering to compute the remainder. The modified query has to
> actually compute all the rows, and sort them, before it knows which are
> the first 100.
Where as I was thinking, well, it would sort the columns by the first bit,
then by the second bit... wrong....
Thank you, I was looking at it from my narrow point of view: sort them by
commit_date, but if there's two identical, sort those two by commit-id...
not the big picture solution.
> If you had an index matching the second ORDER BY clause, you'd probably
> get a plan similar to the first case.
Understood. FWIW, I created an index which uses both items, then did an
ORDER BY commit_date DESC, commit_log_id desc. Yep, I got good times from
that.
But I don't think I'm going to keep this index around for just this one
query. It's used often, for the main page of the website, but given that
I can get around it with slight modification to the select, I think I'll
use that instead.
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-sql@postgresql.org
Cc: dan@langille.org, tgl@sss.pgh.pa.us
Subject: Re: extending ORDER BY make query much slower
In-Reply-To: <20030312173223.E95239@xeon.unixathome.org>
* 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