Re: optimizer question

Bruce Momjian wrote:
>
> > Bruce Momjian wrote:
> > >
> > > > "Reinoud van Leeuwen" <reinoud(at)xs4all(dot)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.

But suppose you could have different indexes on the same column. For
example
for IP address you can theoretically define one index that indexes by
mask
length and other that indexes by numeric value of IP and yet another
that
indexes by some combination of both.

when doing an ORDER BY you can specify 'USING operator'

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

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.

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