Re: [HACKERS] Performance of MIN() and MAX() - Mailing list pgsql-hackers

"Gene Sokolov" <hook@aktrad.ru> writes:
> I was under impression that when max(<primary key>) is called, it should
> just take the value from the index. I believe it should not do any kind of
> scan. But, in fact, it scans the table.

You are mistaken.  Postgres has no idea that min() and max() have any
semantics that have anything to do with indexes.  I would like to see
that optimization myself, but it's not a particularly easy thing to add
given the system structure and the emphasis on datatype extensibility.

> it's a show stopper for us.

You might be able to hack around the issue with queries like
SELECT x FROM table ORDER BY x LIMIT 1;
SELECT x FROM table ORDER BY x DESC LIMIT 1;

to get the min and max respectively.  The current 6.6 code will
implement these with indexscans, although I think 6.5 would not
unless given an additional cue, like a WHERE clause involving x...
        regards, tom lane