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

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
existsand WHERE clause acceptible                        ^^^^^^^^^^^^^^^^^^^^^^^^    ^^^^^^^^^^^^^^^^^^^^^^^
 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026