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.)
>

That's what I thought...

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

Good! That had not occurred to me.

Though one should :
1) be careful with NULL values (excluding them from the select)
2) understand that (of course!) these queries
are VERY inefficient to compute the max/min if
the btree index is not defined.

By the way, I didn't find many comments about the pros and
cons of btree/hash indexes in the docs, nor in Bruce's book...

Regards

Hernan Gonzalez
Buenos Aires, Argentina