如何获取PostgreSQL中正在运行的查询的执行计划？ [英] How to get execution plan for a running query in postgresql?

问题描述

我有一个未优化的查询，该查询在一天中的不同时间（从1分钟到14小时）运行一系列不同的执行时间。 CPU利用率，内存和数据库上的其他并发负载保持不变，这有什么可能导致这种变化？请注意，自动真空过程会在午夜运行，而性能在早晨会大大提高。我的断言是，由于表碎片，死元组和大量读取，同一张表上的统计信息会发生变化，从而生成不同的执行计划。为了证明这一点，我想获取当前正在运行的查询的查询计划。请注意，对于我来说，在执行之前不可能简单地 EXPLAIN 查询。

I have an un-optimized query which runs for a range of different execution times at different times of the day ranging from 1 minute to 14 hours. The CPU utilization, memory and other concurrent load on the database remaining the same, what could result in such variation? Note that auto vacuum process runs during midnight and performance improves considerably in the morning. My assertion is that due to table fragmentation, dead tuples and a lot of reads, the statistics on the same table change which generates a different execution plan. To prove this assertion, I'd like to get the query plan for the current query being run. Note that it is not possible for me to simply EXPLAIN the query before execution.

推荐答案

等等，我知道它可以工作了。在postgresql.conf中，有两个用于预加载库的设置。第一个shared_preload_libraries必须重新启动才能正常工作。但是另一个session_preload_libraries会。因此，编辑postgresql.conf以使其包含以下行：

Wait I got it working. In postgresql.conf there are TWO settings for preloading libraries. The first one, shared_preload_libraries, won't work without a restart. BUT the other one session_preload_libraries WILL. So, edit the postgresql.conf to have this line in it:

session_preload_libraries = 'auto_explain'

然后重新加载：

pg_ctl reload (or pg_ctlcluster 9.x main reload etc)

然后更改数据库以将其打开：

Then alter the database to turn it on:

alter database smarlowe set auto_explain.log_min_duration=1;

然后所有新连接都会获得自动解释的计划。

And then all new connections get auto_explained plans.

将持续时间更改为最适合您的毫秒设置。

Change duration to the millisecond setting that makes most sense for you.

这篇关于如何获取PostgreSQL中正在运行的查询的执行计划？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！