Scott,
> Not exactly. While max(id) is easily optimized by query replacement,
> more complex aggregates will still have perfomance issues that would not
> be present in a row locking database. i.e. max((field1/field2)*field3) is
> still going to cost more to process, isn't it?
Sorry, no.
The issue has nothing to do with MVCC. It has everything to do with the fact
that PostgreSQL allows you to create your own aggregates using functions in
any of 11 languages. This forces the planner to treat aggregates as a
"black box" which does not allow index utilization, because the planner
simply doesn't know what the aggregate is doing internally.
To put it another way, the planner sees SUM() or CONCAT() -- which require
table scans as they must include all values -- as identical to MAX() and
MIN().
Escaping this would require programming a special exception for MAX() and
MIN() into the planner and parser. This has been discussed numerous times
on HACKERS; the problem is, making special exceptions for MAX() and MIN()
would then make it very difficult to implement MAX() or MIN() for new data
types, as well as requiring a lot of debugging in numerous places. So far,
nobody has been frustrated enough to spend 3 months tackling the problem.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
