Josh Berkus <josh@agliodbs.com> writes:
> Tom, why doesn't MAX() use an index?
Don't tell me you haven't seen that discussed many times before :-(
Because Postgres uses an extensible set of aggregate functions, we treat
all aggregates as "black boxes": the implementation strategy is always
to pass all the specified values through the aggregate.
Special-casing MIN and MAX would be nice from the point of view of
performance, but there's this little problem that our sets of datatypes
and index types are also extensible. We'd need to devise some
non-hard-wired way of identifying which aggregate functions are related
to the sort orderings of what indexes.
Finally, the transformation into an optimized form is just not that easy
to do automatically in the general case --- it's easy enough if you
write "SELECT max(col) FROM tab", but how about "SELECT max(col),
min(col) FROM tab"? What if there are WHERE clauses (with or without
constraints on col)? And the GROUP BY case that we started this
discussion with is *very* nontrivial.
This issue is on the TODO list, but given that the ORDER BY/LIMIT
workaround is available (and offers more functionality than MAX/MIN
anyway), I don't think it's a very high-priority problem. We've got
plenty of TODO items for which there is no good workaround...
regards, tom lane