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

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