如何分析 plpgsql 过程
我正在尝试提高长时间运行的 plpgsql 存储过程的性能，但我不知道有哪些可用的分析工具（如果有）。任何人都可以提供有关如何分析此类程序的建议吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
从过程中提出一些通知，包括clock_timestamp() 查看数据库在哪里花费了时间。并使程序尽可能简单。
您能给我们举个例子吗？
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?
我们目前正在寻找这个问题的更好答案，并偶然发现了这个工具：
http://www.openscg.com/2015/02/postgresql-plpgsql-分析器/
主办地点：
https://bitbucket.org/openscg/plprofiler
它声称可以为您提供所需的内容，包括函数每一行所花费的总时间。我们还没有进一步调查，但根据作者的说法，我们持乐观态度。
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.
首先，您可以打开 将所有语句记录到 Postgres 日志文件中。日志将包含每个语句的运行时。通过这种方式，您可以识别最慢的查询并尝试优化它们。
但是阅读您对弗兰克帖子的评论，我猜循环是您的问题。尝试摆脱循环并在单个查询中完成所有操作。一条读取大量行的语句通常比大量只读取几行的语句更有效。
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.
尝试使用 pg_stat_statements 扩展 ( http://www.postgresql.org/docs/9.2 /static/pgstatstatements.html ）。
它可以显示所有语句（包括 plpgsql 过程中的子语句）的调用次数和总调用时间。
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).
要使用的工具是 https://github.com/bigsql/plprofiler
如果您使用PGDG yum 存储库，然后安装 plprofiler 非常简单，只需运行以下命令，但请记住它仅适用于 PostgreSQL 版本 11 及更高版本（将 XX 替换为您的版本号）：
然后添加分析器扩展到您的数据库：
然后要生成有关 plpgsql 函数的配置文件报告，请运行如下命令：
The tool to use is https://github.com/bigsql/plprofiler
If you installed PostgreSQL using the PGDG yum repository, then installing
plprofileris 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):Then add the profiler extension to your database:
Then to generate a profile report on a plpgsql function, run a command like this: