This is an FAQ, BTW -- try searching the archives again. It's also mentioned in the documentation:
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-aggregate.html On Thu, 2003-09-04 at 11:10, Dennis Bjorklund wrote: > On Thu, 4 Sep 2003, Shridhar Daithankar wrote: > > It can not be cached, at least easily. That's the price to pay for MVCC. Same > > goes for select count(*) from table. That query has to end up with a sequential > > scan. > > It does not have to be like that. Even with a mvcc database it can use the > index for max/min and in my opinion it should. Right, AFAIK MVCC isn't relevant to MAX() (given a btree index, you can just read the index in the right order and return the first valid tuple), although it makes optimizing COUNT(*) trickier, I believe. > As far as I know the only reason why it's not implemented in postgresql is > because pg has a general aggregate model and max/min are implemented using > that. Still, max/min are special in that they are almost the only > aggregates that can use an index to deliver the result directly. Some day > someone should make max/min a special case in pg. Exactly how is the > question. Well, it's an open question whether it's worth uglifying the backend to support this optimization, given that there is a trivial workaround that people can use. It would make it easier to port code to PostgreSQL from other RDBMSs, though... -Neil ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
