Re: optimizer question

Bruce Momjian wrote:
> 
> > 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 ...
> 
> Yes, agreed.  This would be just for limited cases.  Updated to:
> 
> * Use indexes for min() and max() or convert to SELECT col FROM tab ORDER
>   BY col DESC LIMIT 1 if appropriate index exists and WHERE clause acceptible
>                          ^^^^^^^^^^^^^^^^^^^^^^^^    ^^^^^^^^^^^^^^^^^^^^^^^
It would be probably a win if only exact match of
 SELECT MAX(*) FROM TAB ;

would be rewritten if appropriate index exists.

The appropriateness should be explicitly declared in aggregate
definition.

-----------------
Hannu