Re: Procedural Code Profiling

> What is the best tool for an app to profile procedural code in
> postgres? I want to instrument my code and trace it so that I can
> see which code bits are sucking up the cpu and time. I know I can
> stick messages in the code and gather my own stats but I was
> wondering is there are any good tools to automate this.
>
> Specifically working with pl/pgSQL and 8.2 (on Linux or windows) if
> that helps.
>
Hi Lewis,

The edb-debugger project at pgFoundry offers three different plugins: a
PL/pgSQL debugger, a PL/pgSQL profiler, and a PL/pgSQL tracer.

EnterpriseDB just open-sourced the debugger last week and we are still
ironing out a few deployment glitches here and there. pgAdmin3 offers
an excellent graphical interface to the PL/pgSQL debugger. The debugger
is usable today, but you'll experience one bug for certain (an extra
"Step" is required at the end of each PL/pgSQL function).

The PL/pgSQL profiler is what you are looking for and, to be honest, I
haven't even tried to compile it in over a year so it is probably pretty
rusty.

The profiler watches (all of) your PL/pgSQL code as it executes and records:

execution count (number of times each statement is executed)
total execution time (how long did we spend executing each statement?)
longest execution time (how long did the slowest iteration take?)
number of scans (total number of sequential and indexed scans)
blocks fetched (for each statement)
blocks hit (blocks found in buffer pool) (for each statement)
tuples returned (for each statement)
tuples fetched (for each statement)
tuples inserted (for each statement)
tuples updated (for each statement)
tuples deleted (for each statement)

The results are written to an XML file (along with the source code for
each PL/pgSQL function that execute) and you can massage that into
whatever form you like (I think I have an XSLT script around here
somewhere that will translate the XML file into a nice HTML report).

I will try to get the profiler compiled against 8.2.4 in the next couple
of days (and send you an e-mail when I get it running).

-- Korry

--
Korry Douglas korryd(at)enterprisedb(dot)com <mailto:korryd(at)enterprisedb(dot)com>
EnterpriseDB http://www.enterprisedb.com