Обсуждение: User-defined Aggregate function and performance.

Hello.

I've tried asking this on the irc channel, without much success.

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.

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.

Any idea on how to make it faster ?

Here is the code:

create type _weighted_avg_type as (
  running_sum numeric,
  running_count numeric
);

create or replace function mul_sum (a _weighted_avg_type, amount
numeric, weight numeric) returns _weighted_avg_type as $$
    select ((($1.running_sum + ($2 * $3)) ,  ($1.running_count +  $3)
))::_weighted_avg_type;
$$ language sql immutable;

create or replace function final_sum (a _weighted_avg_type) returns
numeric as $$
  SELECT CASE
      WHEN $1.running_count = 0 THEN 0
      ELSE $1.running_sum / $1.running_count
  END;
$$ language sql immutable;

create aggregate weighted_avg (numeric, numeric)(
  sfunc = mul_sum,
  finalfunc = final_sum,
  stype = _weighted_avg_type,
  initcond = '(0,0)'
);

create temp table test as (select a::numeric, b::numeric from
generate_series(1, 100) as t1(a), generate_series(1, 100) as t2(b));

-- Custom aggregate
select weighted_avg(a, b) from test;


-- pure sql version
select case when sum(b::numeric) = 0 then 0 else sum(a::numeric *
b::numeric) / sum(b::numeric) end from test;


--
Ronan Dunklau

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

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

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