User-defined Aggregate function and performance.

[Ronan Dunklau <rdunklau@xxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general