Justin,
I'm really not a expert in this area, on how to code this, or functions in
PostgreSQL. All I could offer is some ideas which you might try. Sometimes
this is all it takes. Perhaps someone else will respond that might be more
helpful. With that said I have read in the documentation the use of other
languages and if you are looking for a higher performance, that is the
way I would said its going to come about perhaps.
danap.
> DMP you did give me an idea on changing how to call the append array
> sfunc looks like this
>
> create or replace function wcost_average_sf (numeric[], numeric,
> numeric)
> returns numeric[] as
> $Body$
> begin
> return array_append(array_append($1, $2), $3);
> end;
> $Body$
> LANGUAGE 'plpgsql' VOLATILE;
>
> this yanked out 140,000 ms on the run time, a big improvement but
> no where i want it to be.
>
> are there speed improvements in the other languages TCL
>
> dmp wrote:
>
>> Array appends are usually a performance hit, as you said. I'm not
>> sure though with
>> PostgreSQL. Why not try it with two arrays and see what happens. At
>> least you would
>> reducing the single array and the eliminating the append.
>>
>> danap.
>>
>>> I got the aggregate function for weighted average done. I finely
>>> left alone for more than 10 minutes to actual get it written. It
>>> takes 2 value input Weight and the Value. it will sum the weighted
>>> entries to create the constant then does the normal formula, but
>>> does not percentage number but averaged number. A simple change on
>>> the return line it can do percentages.
>>>
>>> I did a couple of things a little odd . instead of doing a multi
>>> dimensional array i did a one dimensional array where the 1st row is
>>> Weight and the 2nd row is Value. This made the loop through the
>>> array look stupid.
>>> I tested it across 50,000 records with a group by it took 3.3
>>> seconds to run.
>>>
>>> without the group by clause performance is terrible taking several
>>> minutes just to do the sfunc part. 371,563ms
>>>
>>> The Array seems to have performance hit any advice? It could be
>>> the way i'm appending to the Array which has a performance hit as
>>> the array gets bigger and bigger ?
>>
>>
>>
