Re: Combining Aggregates

On 20.2.2015 21:23, Peter Eisentraut wrote:
> On 2/20/15 3:09 PM, Tomas Vondra wrote:
>> On 20.2.2015 21:01, Peter Eisentraut wrote:
>>> Is there a case where the combining function is different from the
>>> transition function, other than for count?
>>
>> It's different in all the cases when the aggregate state is not
>> identical to a single value - for example the usual avg(), sum() and
>> stddev() aggregates keep state which is equal to
>>
>> {count(X), sum(X), sum(X*X)}
>>
>> The 'combine' function gets two such 'state' values, while transition
>> gets 'state' + next value.
>
> That's just because the count is hidden there in an opaque custom
> transition function. If, say, we had instead an array of transition
> functions {inc, plus, plussq} and we knew that plus and plussq are
> associative operators, all we'd need to special case is the count
> case. This would avoid a lot of repetitive code for stddev, avg, etc.

Ummm, I'm not entirely sure I understand that, but the main point was
that the current implementation does not work like that. We have no idea
what transition functions are transitive, and we do have opaque
aggregate states.

Also, there are aggregate functions like array_agg() or string_agg()
that make this impossible, just like for many custom aggregates (like
hyperloglog for example). Again, I might not understand the idea
correctly ...

> (As a bonus, you could use this knowledge to compute count, sum, avg,
> and stddev in parallel using only three counters.)

Yeah, sharing the aggregate state by multiple aggregates would be nice.

regards

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services