Re: btree index and max()

leonbloy@sinectis.com.ar writes:
> I understand that the query planner cannot be so clever
> to grasp that this particular function (max or min)
> might be evaluated by just travelling the BTREE index.
> Am I correct?

You are correct --- the system has no idea that there is any
connection between the MIN and MAX aggregates and the sort order
of any particular index.  (In fact, the system knows nothing
about the specific semantics of any aggregate function; they're
all black boxes, which is a very good thing for most purposes.)

However, if you think of your problem as "how can I use the sort order
of this index to get the min/max?", a semi-obvious answer pops out:

SELECT foo FROM table ORDER BY foo LIMIT 1;        -- get the min
SELECT foo FROM table ORDER BY foo DESC LIMIT 1;    -- get the max

and the 7.0 optimizer does indeed know how to use an index to handle
these queries.

Perhaps someday we will try to convert simple uses of MIN/MAX into
queries like these, but for now, you gotta do it by hand.

            regards, tom lane