Re: using max() aggregate

Louis-David Mitterrand <cunctator@apartia.ch> writes:
> On Fri, Jun 16, 2000 at 02:05:53AM -0400, Tom Lane wrote:
>> Perhaps
>> select * from auction order by stopdate desc limit 1;
>>
>> In 7.0 this should even be pretty quick, if you have an index on
>> stopdate.

> Thanks, yours seems to be the best solution.

> The performance hit between max() and ORDER BY should be roughly
> comparable?

No!  That's why I muttered about an index.  max() has to scan the
whole table (at least in its current incarnation).  ORDER BY with
LIMIT should be implemented as an indexscan that's only run for
one tuple --- in other words, the system basically reaches into
the index, pulls out the last entry, and you're done.

OTOH, if you don't have an index, then the ORDER BY has to be
implemented as a sequential scan followed by sort, which will surely
be slower than just a sequential scan --- for a large table it
will lose even compared to two sequential scans, which is what
you're really looking at for the subselect-based versions.

Either way, the performance is not very comparable...

BTW you need to be running 7.0.* to get the smart plan for
ORDER BY + LIMIT, the pre-7.0 optimizer would miss it in
many cases.

            regards, tom lane