Re: Seqscan in MAX(index_column) - Mailing list pgsql-hackers

From Dennis Bjorklund
Subject Re: Seqscan in MAX(index_column)
Date
Msg-id Pine.LNX.4.44.0309041703110.14280-100000@zigo.dhs.org
Whole thread Raw
In response to Re: Seqscan in MAX(index_column)  ("Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>)
Responses Re: Seqscan in MAX(index_column)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: "Shridhar Daithankar"
Date:
Subject: Re: Seqscan in MAX(index_column)
Next
From: Bruce Momjian
Date:
Subject: Re: Win32 native port