Re: btree index and max()

> leonbloy(at)sinectis(dot)com(dot)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