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