Re: Seqscan in MAX(index_column)

On Thu, 4 Sep 2003, Shridhar Daithankar wrote:

> > column? I think MAX() does not know or cares if a column is indexed, but...
>
> No. Postgresql uses MVCC which mean there could be multiple views of sample
> tuple active at the same time. There is no way to tell which is max. value for
> a column as definition of a committed value can be a moving target.
>
> 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.

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.

I don't know mssql much, but I guess you can't define your own aggregate
functions there? Then all aggregate functions are special anyway.

--
/Dennis