Re: [PATCHES] Avg performance for int8/numeric

Tom Lane wrote:
> Mark Kirkwood <markir(at)paradise(dot)net(dot)nz> writes:
>> ... Having said that - the profiles suggest that we are perhaps doing
>> a whole lot more alloc'ing (i.e copying? detoasting?) of memory for
>> numerics than perhaps we need...
>
> Yeah. We've looked at this in the past and not figured out any
> particularly desirable answer for variable-size accumulator state.
> There is a hack in there for fixed-size pass-by-reference state (see
> count(*)). It's possible that you could get some traction by only doing
> a palloc when the state size has to increase --- with a custom state
> type it'd be possible to keep track of the allocated size as well as the
> actual current length of the numeric sum. Another idea to consider in
> this context is avoiding repeated numeric pack/unpack overhead by
> storing the running sum in the "calculation variable" format, but I'm
> not sure how deep you'd need to burrow into numeric.c to allow that.
>

Right - I figured it would probably be hard :-(. It looks worth
investigating, but might be a more longer term project (for me anyway,
lots of stuff to read in there!).

> 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. I think
> you can find the thread by searching for a discussion of using type
> "internal" as the state datatype --- which is an idea that doesn't work
> by itself because of the security holes it'd open in the type system.
>

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....).

Cheers

Mark