【PostgreSQL】AUTO_EXPLAIN - 慢速查询的日志执行计划

本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

一、介绍

在本文中，我们将了解 PostgreSQL AUTO_EXPLAIN功能的工作原理，以及为什么应该使用它来收集在生产系统上执行的 SQL 语句的实际执行计划。

二、SQL执行计划

当您向 PostgreSQL 发送 SQL 语句时，该语句的执行方式如下图所示：

首先，基于文本的 SQL 语句被解析为可由数据库服务器以编程方式遍历的抽象语法树（例如查询树）。

其次，优化器使用查询树生成最佳执行计划，该执行计划消耗最少的资源来生成所需的结果。

第三，执行器运行执行计划，并将输出作为查询结果集返回给客户端。

三、估计执行计划和真实的执行计划

当您使用 EXPLAIN 命令时。 PostgreSQL 仅返回估计执行计划，即优化器认为对于所提供的 SQL 语句最有效的计划。但是，当您运行 EXPLAIN 命令时，该语句并未真正执行。

另一方面，如果我们运行 EXPLAIN ANALYZE，PostgreSQL 会运行该语句，因此我们将得到实际执行计划，其中还包含执行计划中每个操作的计时信息。

在研究生产系统上的慢速查询时，我们可能会面临几个挑战。

• 首先，出于安全原因，我们可能不允许在生产系统上运行查询，因此，在这种情况下，我们不能简单地运行 EXPLAIN ANALYZE 命令来获取实际执行计划。

• 其次，即使我们有权运行 EXPLAIN ANALYZE 命令，我们也可能会观察到与客户抱怨的计划不同的计划。这可能是由于几个原因造成的。

例如，PostgreSQL 有一个prepareThreshold 设置，其默认值为5。该值告诉PostgreSQL 在切换到使用通用计划的服务器端Prepared Statement 之前，它可以在客户端模拟Prepared Statement 多少次。

如果慢速查询使用通用计划，即使运行 EXPLAIN ANALYZE（它会动态生成执行计划），您也可能无法获得相同的计划。

因此，分析慢速查询的一个更好的解决方案是，我们获取 PostgreSQL 在运行相关查询时使用的实际执行计划。

四、Auto Explain 模块

PostgreSQL 是非常可定制的，它提供了几个我们可以显式激活的扩展。

auto_explain 就是这样的一个扩展，它允许我们捕获花费时间超过预定义阈值的 SQL 查询的实际执行计划。

auto_explain模块提供了一种自动记录执行计划的方法，而无需手动运行。这对于在大型应用程序中追踪未优化的查询特别有用。

该模块不提供SQL可访问的功能。要使用它，只需将其加载到服务器中。您可以将其加载到单个会话中：

您必须是超级用户才能执行此操作。更典型的用法是通过在 postgresql.conf 中的 session_preload_libraries 中包含 auto_explain 或 shared_preload_libraries 来将其预加载到部分或所有会话中。然后，无论查询何时发生，您都可以跟踪异常缓慢的查询。当然，这需要付出一定的管理费用。

4.1 安装

支持 PostgreSQL 版本 12 及更高版本。

在继续之前安装 PostgreSQL。确保有 pg_config 二进制文件，它们通常包含在 -dev 和 -devel 包中。

如果下载失败，可直接下载ZIP包上传解压安装

4.2 配置参数

有几个配置参数控制 auto_explain 的行为。请注意，默认行为是不执行任何操作，因此如果您想要任何结果，则必须至少设置 auto_explain.log_min_duration。

auto_explain.log_min_duration（整数）

auto_explain.log_min_duration 是将导致记录该语句的计划的最短语句执行时间（以毫秒为单位）。将其设置为 0 会记录所有计划。 -1（默认值）禁用计划记录。例如，如果将其设置为 250 毫秒，则将记录运行 250 毫秒或更长的所有语句。只有超级用户才能更改此设置。

auto_explain.log_parameter_max_length（整数）

auto_explain.log_parameter_max_length 控制查询参数值的记录。值为 -1（默认值）会完整记录参数值。 0 禁用参数值记录。大于零的值会将每个参数值截断为那么多字节。只有超级用户才能更改此设置。

auto_explain.log_analyze（布尔值）

auto_explain.log_analyze 会导致在记录执行计划时打印 EXPLAIN ANALYZE 输出，而不仅仅是 EXPLAIN 输出。该参数默认关闭。只有超级用户才能更改此设置。

注意：当此参数打开时，所有执行的语句都会按计划节点计时，无论它们运行的时间是否足够长以实际记录日志。这可能会对性能产生极其负面的影响。关闭 auto_explain.log_timing 可改善性能成本，但代价是获取的信息较少。

auto_explain.log_buffers（布尔值）

auto_explain.log_buffers 控制在记录执行计划时是否打印缓冲区使用统计信息；它相当于 EXPLAIN 的 BUFFERS 选项。除非启用 auto_explain.log_analyze，否则此参数无效。该参数默认关闭。只有超级用户才能更改此设置。

auto_explain.log_wal（布尔值）

auto_explain.log_wal 控制在记录执行计划时是否打印 WAL 使用统计信息；它相当于 EXPLAIN 的 WAL 选项。除非启用 auto_explain.log_analyze，否则此参数无效。该参数默认关闭。只有超级用户才能更改此设置。

auto_explain.log_timing（布尔值）

auto_explain.log_timing 控制在记录执行计划时是否打印每个节点的计时信息；它相当于 EXPLAIN 的 TIMING 选项。重复读取系统时钟的开销可能会显着减慢某些系统上的查询速度，因此当仅需要实际行计数而不是精确时间时，将此参数设置为关闭可能很有用。除非启用 auto_explain.log_analyze，否则此参数无效。该参数默认开启。只有超级用户才能更改此设置。

auto_explain.log_triggers（布尔值）

auto_explain.log_triggers 导致在记录执行计划时包含触发器执行统计信息。除非启用 auto_explain.log_analyze，否则此参数无效。该参数默认关闭。只有超级用户才能更改此设置。

auto_explain.log_verbose（布尔值）

auto_explain.log_verbose 控制在记录执行计划时是否打印详细信息；它相当于 EXPLAIN 的 VERBOSE 选项。该参数默认关闭。只有超级用户才能更改此设置。

auto_explain.log_settings（布尔值）

auto_explain.log_settings 控制在记录执行计划时是否打印有关修改的配置选项的信息。输出中仅包含影响查询计划且其值与内置默认值不同的选项。该参数默认关闭。只有超级用户才能更改此设置。

auto_explain.log_format（枚举）

auto_explain.log_format 选择要使用的 EXPLAIN 输出格式。允许的值为 text、xml、json 和 yaml。默认为文本。只有超级用户才能更改此设置。

auto_explain.log_level（枚举）

auto_explain.log_level 选择 auto_explain 将记录查询计划的日志级别。有效值为 DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、NOTICE、WARNING 和 LOG。默认为日志。只有超级用户才能更改此设置。

auto_explain.log_nested_statements（布尔值）

auto_explain.log_nested_statements 导致考虑记录嵌套语句（在函数内执行的语句）。当它关闭时，仅记录顶级查询计划。该参数默认关闭。只有超级用户才能更改此设置。

auto_explain.sample_rate（实数）

auto_explain.sample_rate 导致 auto_explain 仅解释每个会话中的一小部分语句。默认值为 1，表示解释所有查询。如果是嵌套语句，则要么全部解释，要么不解释。只有超级用户才能更改此设置。

在日常使用中，这些参数是在 postgresql.conf 中设置的，尽管超级用户可以在自己的会话中即时更改它们。典型用法可能是：

4.3 示例

1）创建扩展

2）窗口1执行以下查询

3)窗口2查看当前正在执行的SQL

可以看到PID=43144的进程，正在执行SQL：select pg_sleep(3),relname from pg_class;

执行计划为全表扫描，并且该真实的执行计划不需要通过explain analyze获取。