DeepSeek总结的PostgreSQL 中的 SQL 与 PL/pgSQL：计划缓存

PostgreSQL 中的 SQL 与 PL/pgSQL：计划缓存

在 PostgreSQL 16 上，一个由 PL/pgSQL 循环逐行调用的 SQL 函数每次调用都会被重新规划，而 PL/pgSQL 会缓存其计划。

D
Dinesh
2026 年 10 月 27 日
·
8 分钟阅读

在最近对 PostgreSQL 16 上的一个报表工作负载进行负载测试时，我们发现了一些有趣的事情。

一个非常简单的 SQL 函数，只有一行，包含一个正则表达式，被一个每小时运行一次的作业调用了 1000 万次。每次运行都会让一个 CPU 核心满负荷运行约 47 秒。我们通过将该函数从 LANGUAGE sql 转换为 LANGUAGE plpgsql 修复了这个问题。函数内部的查询没有改变，作业没有改变，作业返回了相同的结果，但 CPU 消耗减少了一半。

这一案例背后的行为已经在 PostgreSQL 18 中修复。不过，许多生产系统仍在运行 PostgreSQL 16 或 17，因此这个问题值得回答：在不改变函数查询的情况下，仅仅改变函数的语言，是如何将其 CPU 成本减半的？

工作负载：一个每小时运行一次的错误仪表盘#

应用将其日志写入 PostgreSQL：调试消息、请求日志、警告以及带有完整堆栈跟踪的错误。每天大约有 1000 万行到达。

CREATE TABLE app_logs (
    id        bigint PRIMARY KEY,
    logged_at timestamptz NOT NULL,
    app       text NOT NULL,
    level     text NOT NULL,
    message   text NOT NULL
);

复制

为了负载测试，我们加载了 24 小时的日志：1000 万行，1.2 GB。

一行错误日志看起来像这样：

[ERR-1040] payment gateway call failed: timeout after 3000 ms
    at com.shop.pay.GatewayClient.call(GatewayClient.java:88)
    at com.shop.pay.PaymentService.charge(PaymentService.java:142)
    at com.shop.api.CheckoutController.submit(CheckoutController.java:57)

复制

错误代码始终是 ERR- 后跟四位数字，因此正则表达式 ERR-[0-9]{4} 可以找到它。

每小时，一个作业会刷新一个“过去 24 小时错误”仪表盘。它读取过去 24 小时的日志，从每一行中提取错误代码，并按代码统计错误数量。每次运行就是 1000 万次函数调用。

CPU 峰值背后的函数#

错误代码由一个小型辅助函数提取，这是许多代码库中常见的那种：

CREATE FUNCTION get_error_code(p_message text) RETURNS text
LANGUAGE sql AS $$
    SELECT (regexp_matches(p_message, 'ERR-[0-9]{4}'))[1]
$$;

复制

每小时作业为每一行日志调用它一次：

DO $$
DECLARE
    r        record;
    v_code   text;
    v_errors bigint := 0;
BEGIN
    FOR r IN
        SELECT message FROM app_logs
        WHERE logged_at >= now() - interval '24 hours'
    LOOP
        SELECT get_error_code(r.message) INTO v_code;

        IF v_code IS NOT NULL THEN
            v_errors := v_errors + 1;    -- 实际作业在这里按错误代码统计
        END IF;
    END LOOP;

    RAISE NOTICE 'error lines found: %', v_errors;
END
$$;

复制

循环遍历全部 1000 万行，并报告 error lines found: 1000000。

修复：用 PL/pgSQL 编写同一个函数，解决了 CPU 峰值#

CREATE OR REPLACE FUNCTION get_error_code(p_message text) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
    v_code text;
BEGIN
    SELECT (regexp_matches(p_message, 'ERR-[0-9]{4}'))[1] INTO v_code;
    RETURN v_code;
END
$$;

复制

CREATE OR REPLACE 保留了函数的名称和参数，因此每小时作业完全没有改变。函数内部的查询也没有改变，两个版本返回相同的结果。第 1 行是没有错误代码的调试行，第 90 行是错误行：

SELECT id, level, get_error_code(message) AS error_code
FROM app_logs
WHERE id IN (1, 90);

--  id | level | error_code
-- ----+-------+------------
--   1 | DEBUG |
--  90 | ERROR | ERR-1040

复制

结果#

我们的负载测试环境是 PostgreSQL 16.15，使用默认设置。我们用每个版本的函数各运行了三次每小时作业：

CPU 时间是 PostgreSQL 为作业报告的用户时间加系统时间，设置 log_statement_stats = on。运行时间与 CPU 时间相同：数据库进程从开始到结束使用一个 CPU 核心的 100%。

每小时运行的 CPU 时间

47 秒 → 24 秒 PostgreSQL 16

相同的作业，相同的查询，相同的结果。只有函数的语言改变了。

为什么改变语言就修复了它#

在 PostgreSQL 运行查询之前，它会制定一个计划：它将遵循的计算结果的步骤。即使是一行查询也有计划，而制定计划是纯粹的 CPU 工作。对于像我们这样的查询，大约需要一微秒。做一次，没什么。每小时做一千万次，就积少成多了。

在 PostgreSQL 18 之前，也就是版本 16 和 17 中，PostgreSQL 对两种语言处理计划的方式不同。

PL/pgSQL 保留其计划。当 PL/pgSQL 函数在一个会话中第一次运行查询时，PostgreSQL 将其保存在计划缓存中。对于前五次调用，它使用真实的消息文本进行规划。在第六次调用时，它制定一个适用于任何消息的通用计划。如果通用计划不比原来更昂贵，PostgreSQL 就在之后的每次调用中使用它。总共只有六个计划，无论循环访问多少行。

PostgreSQL 16 上的 SQL 函数则不是这样。它没有计划缓存。它只保留其计划，直到调用它的语句结束。在每小时作业中，SELECT get_error_code(r.message) INTO v_code 对每一行都是一个新语句。因此对于每一行，PostgreSQL 都会解析函数体、规划其查询、运行一次，然后丢弃所有这些。

循环如何调用函数很重要。一个普通的赋值 v_code := get_error_code(r.message); 会在整个运行期间保持调用存活，因此 SQL 函数只被规划一次。SELECT … INTO、PERFORM 和 EXECUTE 每次都启动一个新查询。

为什么这么简单的函数也要被规划？#

PostgreSQL 通常不会单独规划一行 SQL 函数。它将函数体复制到调用查询中，这称为内联（inlining），函数就消失了。这里没有发生这种情况，原因是 regexp_matches。它是一个返回集合的函数（它返回零行或多行），而 PostgreSQL 不会内联函数体调用它的 SQL 函数。EXPLAIN 显示函数调用留在计划中：

EXPLAIN (VERBOSE, COSTS OFF)
SELECT get_error_code(message) FROM app_logs WHERE id = 90;

--  Index Scan using app_logs_pkey on public.app_logs
--    Output: get_error_code(message)
--    Index Cond: (app_logs.id = 90)

复制

现在将 regexp_matches 改为 regexp_match，它返回单个结果而不是集合（自 PostgreSQL 10 起可用）：

CREATE OR REPLACE FUNCTION get_error_code(p_message text) RETURNS text
LANGUAGE sql AS $$
    SELECT (regexp_match(p_message, 'ERR-[0-9]{4}'))[1]
$$;

-- 同样的 EXPLAIN 现在显示：
--  Index Scan using app_logs_pkey on public.app_logs
--    Output: (regexp_match(message, 'ERR-[0-9]{4}'::text))[1]
--    Index Cond: (app_logs.id = 90)

复制

函数现在被内联了，因此没有函数计划需要重复。使用这个版本，同样的每小时作业使用了 11.9 秒的 CPU，是 PL/pgSQL 版本的一半。当一个函数可以这样编写以便 PostgreSQL 内联它时，那是更好的修复方法。

用 pg_stat_statements 观察#

你不必凭空相信这一点。pg_stat_statements 统计每个语句被执行了多少次以及被规划了多少次。两个设置使其包含函数内部的查询：

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all            # 包含函数内部的语句
pg_stat_statements.track_planning = on    # 统计计划以及执行

复制

重置计数器，运行每小时作业，然后查看语句：

SELECT pg_stat_statements_reset();

-- 在这里运行每小时作业

SELECT calls, plans, query
FROM pg_stat_statements
WHERE query LIKE 'SELECT (regexp_matches%';

复制

这是我们在 PostgreSQL 16 上看到的，每个版本的函数各一次（pg_stat_statements 将常量显示为 $2 和 $3）：

如结果所示，PL/pgSQL 版本仅规划了查询 6 次，而 SQL 版本生成了 10,000,000 次执行计划，每行一次。CPU 就忙在这里：为每个语句生成新计划。

总结#

• 在 PostgreSQL 16 上，无法内联的 SQL 函数只保留其计划，直到调用它的语句结束。
• 每小时作业用 SELECT … INTO 调用它，每行一个新语句：每次运行 10,000,000 个计划。
• PL/pgSQL 将其计划保存在缓存中，对同一查询只规划了六次。
• 将函数转换为 PL/pgSQL 将每次运行的 CPU 时间从约 47 秒减少到约 24 秒，结果相同。
• 改变函数的语言是在不触碰系统其余部分的情况下修复单个函数的一种方法。函数也可以携带自己的设置：ALTER FUNCTION SET work_mem 展示了一个函数的排序如何在不做全局更改的情况下停止溢出到磁盘。

参考资料#

• PostgreSQL 18.0 发行说明，“Improve SQL-language function plan caching”：https://www.postgresql.org/docs/release/18.0/
• 背后的提交，“Change SQL-language functions to use the plan cache”：https://www.postgresql.org/message-id/E1u02Tn-002Kp0-0Q@gemulon.postgresql.org
• PostgreSQL 16 文档，PL/pgSQL 计划缓存：https://www.postgresql.org/docs/16/plpgsql-implementation.html
• PostgreSQL 16 文档，regexp_match 和 regexp_matches：https://www.postgresql.org/docs/16/functions-matching.html
• PostgreSQL 16 文档，pg_stat_statements：https://www.postgresql.org/docs/16/pgstatstatements.html

本页内容

• 工作负载：一个每小时运行一次的错误仪表盘#
• CPU 峰值背后的函数#
• 修复：用 PL/pgSQL 编写同一个函数，解决了 CPU 峰值#
• 结果#
• 为什么改变语言就修复了它#
• 为什么这么简单的函数也要被规划？#
• 用 pg_stat_statements 观察#
• 总结#
• 参考资料#

分享

D
作者

Dinesh
CortexRidge 创始人

15 年以上领导数据库工程团队的经验。在 AWS、Oracle（OCI PostgreSQL）、Tessell、OpenSCG 和 EnterpriseDB 构建和运营过 PostgreSQL 平台。PL/pgSQL 书籍的作者。现在在 CortexRidge 构建 CortexBolt。

Dinesh 的所有文章