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