Re: select max(column) not using index

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

Nirvana: Zustand des Gluecks durch Ausloeschung des Selbst.
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
mail pgp-key-request(at)hostmaster(dot)org

On Thu, 7 Mar 2002, 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!

I real man would proffer criticism in diff -u format.

Gavin

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.

On Thu, 2002-03-07 at 18:04, 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.

PostgreSQL is extensible enough that luser can define max() to mean
anything and thus you don't have a general way to optimise it without
breaking some cases.

If you know that max(x) means the biggest x there is and you have a
b-tree index on x you can use:

select x from t order by x desc limit 1;

> Not even Microsoft has implemented such a design deficiency yet and

It would be a very microsofty way to optimise in ways that sometimes
produce wrong results ;)

> even MySQL which you like to talk so bad about uses an
> index to optimize select max() queries.

What do you need the superfast max() for ?

If you are trying to re-implement sequences you may yet find some
surprises.

> 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!

Did you make yourself look bad by assuming that postgreSQL _does_ your
suggested optimisation ?

> Nirvana: Zustand des Gluecks durch Ausloeschung des Selbst.

How is this related to above ??

-------------
Hannu

On Thu, 7 Mar 2002, 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!

I'm not sure why I'm bothering to respond, but...

Given that postgres allows user defined aggregates and I guess it'd be
possible for a user to redefine max into some form where the optimization
isn't valid (I'm not sure why mind you, but...) that'd mean that the
optimization is not always available. Personally, I'd generally prefer
correct and slow over incorrect and fast. I'm fairly sure that if you made
a patch that cleanly dealt with the issue without programming in special
knowledge of min and max it'd be considered for inclusion.