Re: optimizer question

Hannu Krosing <hannu@tm.ee> writes:
> Maybe rather

> * Use indexes for min() and max() or convert to "SELECT col FROM tab
>   ORDER BY col DESC USING max_index_op LIMIT 1" if there is an index 
>   on tab that uses btree(col max_index_op)

> it seems that in most other cases the rewrite would be either a 
> misoptimisation or plain wrong.

We would clearly need to add information to the system catalogs to allow
the planner to determine whether a given aggregate matches up to a given
index opclass.  This has been discussed before.

A more interesting question is how to determine whether such a rewrite
would be a win.  That is NOT a foregone conclusion.  Consider
SELECT max(col1) FROM tab WHERE col2 BETWEEN 12 AND 42;

Depending on the selectivity of the WHERE condition, we might be far
better off to scan on a col2 index and use our traditional max()
code than to scan on a col1 index until we find a row passing the
WHERE condition.  I'm not sure whether the planner currently has
statistics appropriate for such estimates or not ...
        regards, tom lane