Re: MAX/MIN optimization via rewrite (plus query rewrites generally)

On Thu, Nov 11, 2004 at 08:00:01AM -0600, Bruno Wolff III wrote:
> On Thu, Nov 11, 2004 at 17:52:19 +1100,
>   John Hansen <john@geeknet.com.au> wrote:
> > Why not just change the function all together to 'select $1 from $2
> > order by $1 desc limit 1;'
> > 
> > Is there ANY situation where max(col) as it is, would be faster?
> 
> Yes. A couple I can think of are:
> When count(col) is also being used.

Technically, wouldn't that depend on how many rows you were processing?
Certainly the time required for the CPU to compare the value of a field
in the current row to what it's got stored as the current maximum is
small compared to a disk read, but at some point it will be faster to
read an index.

> When a GROUP BY is being used and there isn't an index that can both be used
> to do the grouping and col order within each group.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"