Re: [HACKERS] Performance of MIN() and MAX()

"Gene Sokolov" <hook(at)aktrad(dot)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