Re: extending ORDER BY make query much slower

On Wed, 12 Mar 2003, Tom Lane wrote:

> "Dan Langille" <dan(at)langille(dot)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.