Re: select max(column) not using index

Thomas Zehetbauer wrote:
>
> I think you all should really buy the book 'Database Development for Dummies'.
> Postgresql is for sure the only database on this planet that cannot optimize a
> select(max) using an index. Not even Microsoft has implemented such a design
> deficiency yet and even MySQL which you like to talk so bad about uses an
> index to optimize select max() queries. Some of you should really consider
> attending a programming course and all of you should consider to stop working
> on this totally screwed up monster!
>
> Tom

The query:

select max from table order by max desc limit 1

Will do it, but "max()" is by no means an easy to optimize function. Aggregates
have an assumption of a range scan, especially custom aggregates. What about
this:

select max(foo) from bar where x = 'y';

How is the index used in this query?

The only instance where an aggregate optimization would pay off is when there
is no selection criteria and there is an index on the field. In this case, it
is easy enough to create a function for the particular application.

I hear and understand your frustration, yes PostgreSQL should be able to do
that, and maybe it would be worth the time and effort, that is not for me to
say, however there is a very viable work around for the problem you state and
the stated problem, while a common query, is a small subset of the actual
capability of the max() function.