Ronan Dunklau <rdunklau@gmail.com> writes:
> I'm trying to define a "weighted mean" aggregate using postgresql create
> aggregate feature.
> I've been able to quickly write the required pgsql code to get it
> working, but after testing it on a sample 10000 rows table, it seems to
> be approximately 6 to 10 times slower than pure sql.
It might help to use a two-element array for the transition state,
instead of a custom composite type.
> My initial implementation was in pl/pgsql, and did not mark the
> functions as immutable. I did so after a suggestion from an irc user,
> but it did not change anything performance wise.
Those suggestions would possibly help for a function that's meant to be
inlined into larger SQL expressions, but they won't do much for an
aggregate support function. I'm not real sure, but I think plpgsql
might be faster in this context.
Another thing to think about is whether you really need type numeric
here. float8 would be a lot faster ... though you might have roundoff
issues.
regards, tom lane