Bruce Momjian wrote: > > > 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.
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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
