Re: Seqscan in MAX(index_column)

On 4 Sep 2003 at 11:32, Paulo Scardine wrote:

> (Perhaps a newbie question, but I tried to google this out without success).
>
> Why postgres does an expensive seqscan to find the max(value) for an indexed
> column? I think MAX() does not know or cares if a column is indexed, but...
> Should not it? BTW, is there some smarter trick to do that?

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.

>
> I know I can just do a very fast (SELECT pk FROM foo ORDER BY pk DESC LIMIT
> 1) instead, but my coleagues are arguing that MAX(indexed_column) seems to
> be a lot
> more smarter in MS-SQLServer and I end up without a good response.

Well, postgresql earns solid concurrency due to MVCC. Set up postgresql and MS
SQL server on same machine and do a rudimentary benchmark with 100 clients
hitting database hard. See where you get more tps'.s

In postgresql, readers and writers don't block each other. AFAIK, in MS SQL
server rows are ocked for update. So if you lock a row in transaction and does
not commit for long, MS SQL will have serious problems.

All night long transactions are no problem to postgresql except for the fact
that vacuum can not clean the tuples locked in tranactions.

HTH

Bye
Shridhar

--
Blutarsky's Axiom: Nothing is impossible for the man who will not listen to
reason.