Re: Accelerating aggregates

Steve Atkins wrote:
> Stop me if you've heard this before.
> 
> 
> I'm looking at fast calculation of aggregates (sum(), max(), count())
> across large tables, or across fairly simply defined subsets of those
> tables.
> 
> Lets say that, for a given aggregate function on a given table (with a
> given where clause, perhaps), each postgres process maintains a state
> variable (stype, in aggregate terms) and there's a also a single state
> variable available to all backends via shared memory.
> 
> Each time a transaction starts the process initialises its local state
> variable to the initcond of the aggregate. Each time a row is inserted
> into the table the local state variable is updated, using the
> aggregate update function. Each time a row is removed then the local
> state variable is either updated, or invalidated, using a
> "reverse-update" function, again specific to the aggregate.
> 
> If the transaction is rolled back, the local state variable is
> thrown away. If the transaction is commited and the local state
> variable has been invalidated then the global state variable is
> invalidated, otherwise the global state variable is updated using
> a a state merge function, specific to the aggregate.

Isn't this going to have visibility issues wrt other backends? How do I 
know what transactions have updated the global and what haven't and 
which I should currently be seeing?

I'm not sure that there is a solution simpler than the "insert +1/-1 
into summary table" that gets discussed.

--   Richard Huxton  Archonet Ltd