PostgreSQL 教程: 使用 EXPLAIN 解释参数化查询

七月 18, 2024

摘要:在本教程中,您将学习如何使用EXPLAIN语句,显示参数化查询的执行计划。

目录

EXPLAIN (GENERIC_PLAN) 的语法

您只能在带括号的选项列表中使用新选项,如下所示:

EXPLAIN (GENERIC_PLAN) SELECT ...;

许多人仍然习惯于旧的、简单的编写选项的方式,例如:

EXPLAIN VERBOSE SELECT ...;

但是,这种编写选项的方式已过时,仅出于兼容性原因受到支持。它不适用于像 “GENERIC_PLAN” 这样的新选项。可对照一下文档中对此的说明:

只能指定ANALYZEVERBOSE选项,并且只能按该顺序指定,且不能带有括在括号中的选项列表。在 PostgreSQL 9.0 之前,不带括号的语法是唯一受支持的语法。预计所有新选项将仅在括号内的语法中受支持。

请注意,不能与ANALYZE一起使用GENERIC_PLAN,因为如果参数值未知,则无法执行查询。

使用 EXPLAIN (GENERIC_PLAN)

可使用GENERIC_PLAN获取包含参数占位符(如 “$1”)的查询的执行计划:

EXPLAIN (GENERIC_PLAN) SELECT * FROM pg_class WHERE relname = $1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.28..8.29 rows=1 width=606)
   Index Cond: (relname = $1)
(2 rows)

如果没有选项GENERIC_PLAN,PostgreSQL 会发出缺少参数值的错误:

EXPLAIN SELECT * FROM pg_class WHERE relname = $1;
ERROR:  there is no parameter $1
LINE 1: EXPLAIN SELECT * FROM pg_class WHERE relname = $1;
                                                       ^

EXPLAIN (GENERIC_PLAN) 什么时候有用?

有两种场景:

  • 在 PostgreSQL 日志中找到的参数化语句
  • pg_stat_statements中找到的参数化语句

对于此类语句,EXPLAIN (GENERIC_PLAN)可以显示执行计划,让您了解语句的执行方式。要完整分析,您通常需要EXPLAIN (ANALYZE, BUFFERS)输出,但这需要找到并替换适当的参数值。如果语句有很多参数,这可能会很麻烦,也许对于第一次分析来说,通用计划已经足够好了。

此外,如果您本身就想检查通用计划,EXPLAIN (GENERIC_PLAN)可能会很有用。这有助于分析预备语句的性能。对于一条预备语句,前五次执行会使用自定义计划,因此您必须对语句进行六次EXPLAIN,才能看到通用计划是什么样(如果 PostgreSQL 切换到了通用计划)。使用EXPLAIN (GENERIC_PLAN)可用更少的工作量,来显示出通用计划。

EXPLAIN (GENERIC_PLAN) 的限制

不能在任何地方使用参数

您可以使用参数占位符,例如$1,代替未知值或变量值。但有一定的限制:

  • 只能将参数用于语句:SELECTINSERTUPDATEDELETEVALUES
  • 您只能使用参数替代常量(文本)。您不能使用参数来代替标识符(对象名称),或关键字等。

下面语句是可以的:

SELECT date_part($1, d)
FROM generate_series($2, current_timestamp, $3) AS d
LIMIT $4;

但这些语句是无效的:

-- parameters cannot replace identifiers
SELECT col FROM $1;

-- parameters cannot replace the WHERE clause
SELECT col FROM tab $1;

缺少数据类型信息会使语句语义模糊

由于参数占位符没有数据类型,因此某些有效的构造可能会变得不明确:

EXPLAIN (GENERIC_PLAN)
SELECT * FROM generate_series($1, $2, $3);
ERROR:  function generate_series(unknown, unknown, unknown) is not unique
LINE 2: SELECT * FROM generate_series($1, $2, $3);
                      ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

在这里,PostgreSQL 无法推断出,您指的是哪个叫做generate_series的函数,即使下面语句是正确的:

PREPARE stmt(integer, integer, integer) AS
SELECT * FROM generate_series($1, $2, $3);

在这种情况下,您应该添加一个显式的类型强制转换,来消除歧义:

EXPLAIN (GENERIC_PLAN)
SELECT * FROM generate_series($1::integer, $2, $3);
                               QUERY PLAN
------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=4)
(1 row)

结论

从 PostgreSQL 版本 16 开始,如果您需要快速查看参数化语句的执行计划,可以考虑使用EXPLAIN (GENERIC_PLAN)