Re: User-defined Aggregate function and performance.

Ronan Dunklau <rdunklau(at)gmail(dot)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