Date: Wed, 12 Mar 2003 17:48:06 -0500 (EST)
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 
In-Reply-To: <29647.1047506458@sss.pgh.pa.us>
Message-ID: <20030312173223.E95239@xeon.unixathome.org>
References: <3E6F45D8.15284.5994716@localhost>
 <29647.1047506458@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.