> Bruce Momjian wrote: > > > > > "Reinoud van Leeuwen" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
