Greg,
> The only connection to MVCC is that the "obvious" solution doesn't work,
> namely storing a cache of the aggregate in the table information.
Well, that solution also doesn't work if you use a WHERE condition or JOIN,
now does it?
> So what would it take to implement this for "all" aggregates? Where I think
> "all" really just means min(), max(), first(), last().
Um, what the heck are first() and last()? These are not supported aggregates
... table rows are *not* ordered.
> For min() and max() it would have to indicate not only that only the first
> or last record is necessary but also the sort order to impose.
I think Tom already suggested this based on adding a field to CREATE
AGGREGATE. But I think implementation isn't as simple as you think it is.
> Now the problem I see is if there's no index on the sort order imposed, and
> the previous step wasn't a merge join or something else that would return
> the records in order then it's not necessarily any faster to sort the
> records and return only some. It might be for small numbers of records, but
> it might be faster to just read them all in and check each one for min/max
> the linear way.
Yes, Tom mentioned this also. Working out the rules whereby the planner could
decide the viability of index use is a non-trivial task.
--
Josh Berkus
Aglio Database Solutions
San Francisco