Re: optimizer question

Hannu Krosing wrote:
>
> Bruce Momjian wrote:
> >
> > > Hannu Krosing <hannu(at)tm(dot)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.

I want to chime in here. If the ability exists to evaluate that max() or min()
is appropriate, and that using the equivilent of "select select col1 from tab
desc limit 1" for "select max(col1) from tab" would be a huge gain for
Postgres. I know our Oracle8i can't do it, and it would be a very usefull
optimization.

At issue is the the "limit" clause is very very cool and not available in
Oracle, and since it isn't available, one does not think to use it, and in
queries where they my execute on both Postgres AND oracle, you can't use it.