Re: User-defined Aggregate function and performance.

On 02/04/2012 18:06, Tom Lane wrote:
> 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.

It does not change much.
It seems that altering the transition state instead of building a new
one does help, though. When altering the state, the composite type
version seems to be faster. But it still much slower than the
hand-written sql version.

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

The "hand-written" sql using only built-in functions performs really
well with numerics. Why do you suggest that it could be the bottleneck ?

I solved the problem by writing a C extension for it:

http://pgxn.org/dist/weighted_mean/1.0.0/

Regards,

--
Ronan Dunklau