Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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

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

Sue Fitt

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group