Re: [PATCHES] Avg performance for int8/numeric

Tom Lane wrote:
> Mark Kirkwood <markir(at)paradise(dot)net(dot)nz> writes:
>> Tom Lane wrote:
>>> Most of this depends on being able to have a transition state value
>>> that isn't any standard SQL datatype. We've discussed that recently
>>> in I-forget-what-context, and didn't find a good answer yet.
>
>> Interesting, I didn't think of doing that - was considering creating a
>> suitable SQL composite type - a bit crass I guess, but I might just try
>> that out anyway and see what sort of improvement it gives (we can then
>> discuss how to do it properly in the advent that it's good....).
>
> The thing is that (a) composite types have *at least* as much overhead
> as arrays, probably rather more; and (b) a composite type in itself
> doesn't allow non-SQL types as components, so still doesn't let you
> tackle the idea of keeping the running sum in numeric.c's internal
> calculation format. So I don't think this will prove much --- the only
> gain you can get is the count-in-int8-instead-of-numeric bit, which is
> interesting but there is much left on the table.
>

Right - I spent this afternoon coming to pretty much the same conclusion...

So I guess the best way forward is to make do for the time being with
the savings gained by not calculating sumX2, and revisit avg (and
variance etc) when we know how to do non-SQL state types.

Cheers

Mark