How to profile plpgsql procedures

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?

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?

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.

To start with, you could turn on logging of all statements into the Postgres logfile. The log will contain the runtime for each statement. This way you can identify the slowest queries and try to optimize them.

But reading your comment to Frank's post I'd guess that the looping is your problem. Try to get rid of the looping and do everything in a single query. One statement that reads a lot of rows is usually more efficient than a lot of statements reading only a few rows.

Try to use pg_stat_statements extension ( http://www.postgresql.org/docs/9.2/static/pgstatstatements.html ). It can show call number and total call time for all statements (including sub-statements within plpgsql procedures).

The tool to use is https://github.com/bigsql/plprofiler

If you installed PostgreSQL using the PGDG yum repository, then installing plprofiler is very straightforward, just run the commands below but keep in mind it's only available for PostgreSQL versions 11 and higher (replace XX with your version number):

yum install plprofiler_XX-server plprofiler_XX-client plprofiler_XX

Then add the profiler extension to your database:

CREATE EXTENSION plprofiler;

Then to generate a profile report on a plpgsql function, run a command like this:

plprofiler run -U your_username -d your_database --command "SELECT * FROM your_custom_plpgsql_function()" --output profile.html