slow functions

From: Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: slow functions
Date: 2007-01-11 09:19:46
Message-ID: 45A60132.2060607@inf.ed.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi all,

I'm working on a database (postgresql of course) which has a number of
user-defined functions, some in plperl, some in plpgsql and one in
plpython. Some of these seem pretty slow - I'm not experienced with
databases however so I don't know what to expect speed-wise.
In a test example, adding data to about 4 tables via one of these
functions uses:
Total runtime: 11700.112 ms

Anyway, the problem comes down to this. I have functions which call
other functions, which do inserts, and some of those inserts set off
triggers, e.g.

function_a -> function_b -> inserts data in table 1 -> trigger_a
-> inserts data in table 2 -> trigger_b
-> function_c -> inserts data in table 3 -> trigger_c
-> trigger_d

Now, I'd like to know how much time each of these user-defined functions
is taking, preferably with a simple summary for each function and not
including lots of stats I don't need. EXPLAIN ANALYZE only gives the
time for function_a, and EXPLAIN ANALYZE VERBOSE gives output which is
(to me) incomprehensible and doesn't seem to answer my question - btw I
guess this output is documented somewhere but if so I haven't found it,
so maybe the answer is buried in there somewhere? I've tried other
options, such as debugging via the postmaster, but haven't found what I
need.

I can't be the only one with this problem so I guess there must be a
tool to do this somewhere. Any suggestions?

Thanks,
Sue Fitt

Browse pgsql-novice by date

  From Date Subject
Next Message brian stapel 2007-01-11 15:44:01 ODBC Linked Table Problems
Previous Message Devrim GUNDUZ 2007-01-09 19:44:35 Re: Are postgresql 8.2.1 RPMs signed?