Re: [PATCHES] Avg performance for int8/numeric

On Mon, 2006-11-27 at 18:22 +1300, Mark Kirkwood wrote:
> Tom Lane wrote:
> > Mark Kirkwood <markir@paradise.net.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.

IIRC the calculation format for NUMERIC is simply less padded than the
on-disk version. It should be possible to create it as a normal type
that never gets used apart from this situation.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com