Search Postgresql Archives

Re: how do you write aggregate function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 ?




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux