解释PostgreSQL中的参数化语句
xiongcc约 7 分钟解释 PostgreSQL 中的参数化语句
对于详细的查询性能分析，你需要对 SQL 语句进行 EXPLAIN (ANALYZE, BUFFERS) 输出。对于一个参数化的语句，可能很难为 EXPLAIN (ANALYZE) 构建可运行的语句。有时，你甚至不知道参数值。在本例中，我将向你展示如何至少获得通用执行计划的简单的 EXPLAIN 输出。这样的执行计划总比什么都没有好，甚至可能足以猜出问题所在。
参数化语句
通过扩展查询协议，PostgreSQL 允许将 SQL 语句和语句中使用的常量分开。这增强了安全性，因为它使 SQL 注入变得不可能，但它主要是一种性能特性。这样的参数化语句可以用不同的参数值命名和重用，从而节省了数据库引擎一遍又一遍解析相同语句的工作。更重要的是，使用这个特性 PostgreSQL 有时可以避免为每次语句的执行生成执行计划的更大开销。
你通常会在两种情况下遇到参数化语句：
带参数的预备语句（通常通过客户端 API 使用） PL/pgSQL 函数中使用变量的静态 SQL 语句
参数的占位符是 2 等等。对于 PL/pgSQL，你看不到它们，PL/pgSQL 调用处理器会替换这些占位符而不是变量名。注意，参数化语句只能是 SELECT、 INSERT、 UPDATE、 DELETE 和 VALUES 语句。
参数化语句的通用计划
通常，每当查询被执行时，PostgreSQL 都会生成一个执行计划。但是，PostgreSQL 可以缓存命名的参数化语句的执行计划，计划只会缓存在单个数据库会话中；没有共享内存执行计划缓存。
如果 PostgreSQL 认为它可以在不损害性能的情况下这样做，它将开始对语句使用通用计划。这样的执行计划不会考虑参数值，并且可以重用。是否切换到通用计划的决定是基于启发式的（heuristic），通常在语句第六次执行时做出。你可以从它使用的占位符而不是参数来判断这样的计划。下面是一个使用预备语句的例子：
PREPARE stmt(text) AS SELECT oid FROM pg_class WHERE relname = $1; EXPLAIN (COSTS OFF) EXECUTE stmt('pg_proc');
QUERY PLAN
═════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class
Index Cond: (relname = 'pg_proc'::text)
(2 rows)
接下来的四次 EXPLAIN 执行看起来是一样的，但是接着我们看到
EXPLAIN (COSTS OFF) EXECUTE stmt('pg_attribute');
QUERY PLAN
═════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class
Index Cond: (relname = $1)
(2 rows)
PostgreSQL 已经开始使用通用计划了！从那时起，查询计划时间将变得更短。
为参数化语句强制使用通用计划
你可以使用 PostgreSQL 参数 plan_cache_mode 来影响上一节中所描述的行为。默认设置为 "auto" ，选择上述启发式的行为，即 PostgreSQL 在执行几次后决定通用计划是否有益。
通过设置 "force_custom_plan"，你可以告诉 PostgreSQL 永远不要使用通用计划。如果通用计划结果不如 PostgreSQL 认为的那么好，那是个好主意。这也是数据仓库的一个很好的设置，你通常在其中运行昂贵的分析查询并且节省计划时间不如获得最佳的执行计划重要。
最后，设置 "force_generic_plan" 会使 PostgreSQL 立即使用通用计划。稍后我们将使用该设置。
在哪里可以遇到参数化语句？
在 PostgreSQL 日志中的预备语句
在日志中的参数化语句看起来像这样：
LOG: duration: 0.012 ms execute stmt: SELECT oid FROM pg_class WHERE relname = $1
DETAIL: parameters: $1 = 'pg_proc'
通常，参数会被记录为详细的消息，但如果参数很多，用参数值替换所有的占位符可能需要相当大的努力。你也没有在日志中看到参数数据类型，因此你可能必须查找表定义才能知道你应该写 42 还是 '42'。如果该语句导致错误并且你没有将 log_parameter_max_length_on_error 设置为非零值，则根本不会记录参数：
ERROR: canceling statement due to statement timeout
STATEMENT: SELECT oid FROM pg_class WHERE relname = $1
在 pg_stat_statements 中的参数化语句
pg_stat_statements 是分析数据库工作负载的瑞士军刀。它的一个特点是它忽略了常量的值，因此只有常量不同的语句被聚合在一起。因此，如果你查询 pg_stat_statements 视图，你甚至会在最初未参数化的语句中看到占位符。此外，由于 pg_stat_statements 收集一条语句多次执行的统计信息，但它不会收集其中任何一个的实际参数值。
通用计划的必要性
如果你在日志中或 pg_stat_statements 中发现一个问题语句，你想要分析它的性能。为了做到这一点，你必须猜测适当的参数值，以便你可以使用 EXPLAIN (ANALYZE, BUFFERS) 获得执行计划。这可能很乏味并且需要很长时间。
对于第一次分析，查看由 EXPLAIN（没有 ANALYZE）生成的执行计划会很有帮助。由于"普通" EXPLAIN 不执行查询，因此它不应该依赖于实际参数值，只要我们对通用计划感到满意即可。不幸的是，EXPLAIN 拒绝为参数化语句生成通用计划：
EXPLAIN SELECT oid FROM pg_class WHERE relname = $1;
ERROR: there is no parameter $1
LINE 1: EXPLAIN SELECT oid FROM pg_class WHERE relname = $1;
^
即使我们设置 plan_cache_mode = force_generic_plan，我们也会收到该错误。
使用 PREPARE 为参数化语句生成通用计划
我们想做得更好，而且我们可以做到。使用 PREPARE，我们可以创建一个带有占位符的预备语句：
PREPARE stmt(name) AS SELECT oid FROM pg_class WHERE relname = $1;
现在我们可以强制执行通用计划并解释预备语句。我们可以提供 NULL 作为参数值，因为每种数据类型都存在 NULL，并且参数值无论如何都会被忽略：
SET plan_cache_mode = force_generic_plan; EXPLAIN EXECUTE stmt(NULL);
QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.28..8.29 rows=1 width=4)
Index Cond: (relname = $1)
(2 rows)
DEALLOCATE stmt;
唯一剩下的美中不足的是我们必须为参数找出合适的数据类型。
在参数化语句中使用伪类型 unknown
伪类型是不能在表定义中使用的数据类型。其中一种数据类型是"unknown"：它在查询解析字符串常量期间使用，其数据类型必须稍后根据上下文进行解析。我们可以使用 unknown 作为查询参数的数据类型，让 PostgreSQL 自己找出合适的数据类型：
PREPARE stmt(unknown) AS SELECT oid FROM pg_class WHERE relname = $1; SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE stmt(NULL);
QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.28..8.29 rows=1 width=4)
Index Cond: (relname = $1)
(2 rows)
DEALLOCATE stmt;
将他们放入扩展中
现在我们有一个简单的算法来获取参数化语句的通用计划：
计算参数的个数 使用许多"unknown"参数创建预备语句 将 "plan_cache_mode" 设置为 "force_generic_plan" 使用 NULL 作为参数解释预备语句
我将所有这些包装成一个函数并编写了扩展 generic_plan。它是用 PL/pgSQL 编写的，不需要超级用户权限即可安装。在这里你可以看到它的实际效果：
CREATE EXTENSION IF NOT EXISTS generic_plan; SELECT generic_plan('SELECT * FROM pg_sequences WHERE max_value < last_value + $1');
generic_plan
═════════════════════════════════════════════════════════════════════════════════════════════
Subquery Scan on pg_sequences (cost=1.09..24.10 rows=1 width=245)
Filter: (pg_sequences.max_value < (pg_sequences.last_value + $1))
-> Nested Loop (cost=1.09..24.09 rows=1 width=245)
Join Filter: (c.oid = s.seqrelid)
-> Seq Scan on pg_sequence s (cost=0.00..1.06 rows=6 width=49)
-> Materialize (cost=1.09..22.76 rows=3 width=136)
-> Hash Join (cost=1.09..22.74 rows=3 width=136)
Hash Cond: (c.relnamespace = n.oid)
-> Seq Scan on pg_class c (cost=0.00..21.62 rows=8 width=76)
Filter: (relkind = 'S'::"char")
-> Hash (cost=1.05..1.05 rows=3 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..1.05 rows=3 width=68)
Filter: (NOT pg_is_other_temp_schema(oid))
(13 rows)
结论
收集参数值以分析参数化语句的执行可能很复杂，但使用 generic_plan 扩展我们至少可以轻松获得通用计划。使用的技巧是带有 "unknown" 类型参数的预备语句，调整 plan_cache_mode 并使用 NULL 作为参数值。
如果你有兴趣了解有关参数的更多信息，请查看我关于 Query Parameter Data Types and Performance.的博客。
前文译自：https://www.cybertec-postgresql.com/en/explain-that-parameterized-statement/。
小结
对于复杂类的 SQL，由于一些代码框架的问题，你可能会在日志中看到很多诸如 2这种带有绑定变量的 SQL，假如要分析性能问题的话就比较头疼，不仅要去看表的定义，还要一个个代入绑定变量的值，因此一个可行的方式就如前文所说：
使用 "unknown" 伪类型替代变量类型，让 PostgreSQL 自己去找合适的数据类型 输入 NULL 作为参数值 使用 force_generic_plan生成一个通用执行计划
但是如果数据分布倾斜较大，这种方式就不适用了，不过总好过无嘛。