Thread: 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@hostmaster.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'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.
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 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 Thur, March 07, 2002, Thomas Zehetbauer spewed: > > 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! And perhaps you should pick up "Open Source Development for Dummies"... The wonderful thing about open-source projects is that all-talk, no-action critics such as yourself can be told: "If you don't like the way the program works, feel free to download the src and put that programming course you attended to good use by implementing it yourself." I'm sure all of the postgres community welcome your patch for this optimization, Mr. Zehetbauer. Darren
> select max(foo) from bar where x = 'y'; > > How is the index used in this query? Informix would use an index created on (x, foo) and I guess others too. But I too usually find the "select first 1 * from y order by x desc" much more useful than an optimized max, since it can also return other columns from that row (And is more performant than an optimally optimized subselect for this). Andreas