Re: Index not being used in MAX function (7.2.3)

> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno@wolff.to]
> Sent: Wednesday, June 11, 2003 11:30 AM
> To: Jonathan Bartlett
> Cc: Tom Lane; Dann Corbit; jim@nasby.net; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Index not being used in MAX function (7.2.3)
>
>
> On Wed, Jun 11, 2003 at 10:44:22 -0700,
>   Jonathan Bartlett <johnnyb@eskimo.com> wrote:
> > I wonder if a macro system might be warranted - then have max be a
> > macro instead of an aggregate.  However, I don't know
> exactly how that
> > would work since it involves the whole statement.  Anyway, just an
> > idea to hopefully spur someone else's thinking cap :)
>
> I don't think that would work. There are going to be some
> cases where the aggregate is better than the subselect (and
> not just when there isn't an appropiate index). And in some
> cases distinct on order by may be the best way to get what you want.

Isn't that the optimizer's job to figure out?  The whole idea of SQL is
to abstract the queries and allow the optimizer to make all the smart
choices about plans and stuff.

I do realize that it is very "non-trivial" to implement, but min() and
max() are used so often it seems it might be useful.

Here are some "free to use" templates for statistical functions:
ftp://cap.connx.com/tournament_software/Kahan.Hpp
ftp://cap.connx.com/tournament_software/STATS.HPP

The Kahan template is an extremely accurate adder (does not lose
precision like direct summation).
The Stats template (which uses the Kahan adder) does all sorts of things
like skew, kurtosis, min, max, stddev, average, count, sum etc. all
simultaneously.
Our product uses a similar template to produce all kinds of useful
statistical information.  See:
http://www.connx.com/products/connx/Connx%208.8%20UserGuide/connxcdd32.h
tm
And look at the statistical functions book.

No, we don't do the optimization I have suggested for min/max, but I
hope to poke it into our tool set some day.  However, we do have a
function called "sortfirst()" and a function called "sortlast() " both
of which do perform the suggested optimizations [when possible].

Perhaps PostgreSQL could do something similar.