How to profile plpgsql procedures

Question

I'm trying to improve the performance of a long-running plpgsql stored procedure, but I have no idea what, if any, profiling tools are available. Can anyone offer suggestions for how to go about profiling such a procedure?

Answer 1

Raise some notices from the procedure including the clock_timestamp() to see where the database spends time. And make the procedures a simple as possible.

Could you show us an example?

Answer 2

We are currently looking for a better answer to this question, and have stumbled across this tool: http://www.openscg.com/2015/02/postgresql-plpgsql-profiler/ Hosted at: https://bitbucket.org/openscg/plprofiler

It claims to give you what you are looking for, including the total time spent on each line of the function. We have not investigated it further yet, but based on the author's claims, we are optimistic.