Re: optimizer question

> Bruce Momjian wrote:
> > 
> > > "Reinoud van Leeuwen" <reinoud@xs4all.nl> writes:
> > > > I have a table that contains almost 8 milion rows. The primary key is a
> > > > sequence, so the index should have a good distribution. Why does the
> > > > optimizer refuse to use the index for getting the maximum value?
> > >
> > > The optimizer has no idea that max() has anything to do with indexes.
> > > You could try something like
> > >
> > >       select * from tab order by foo desc limit 1;
> > 
> > Can we consider doing this optimization automatically?
> 
> Only if we assume that people do not define their own max() that does
> something 
> that can't be calculated using the above formula like calculating AVG().

I hadn't thought of that one.  I can't imagine a max() that doesn't
match the ORDER BY collating.

Updated TODO item:

* Use indexes for min() and max() or convert to SELECT col FROM tab ORDER BY col DESC LIMIT 1;   

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