要诊断重复的 PostgreSQL 查询，请将候选集的构建与使用分开考察。查看内层扫描的执行次数，检查缓存是否避免了重复工作，然后比较分组复用、CTE 内联或带索引的临时表。

本指南面向使用 Mac、需要解释一条运行缓慢的文章查询的开发者：先选取候选文章一次，再找出每篇文章所属主题中的最高候选文章 ID。全文使用合成数据行。目标是确定哪些工作在重复，并选择一种能够保留查询结果的查询结构。

设置可复现的候选查询

使用已有的本地 PostgreSQL 安装，并确保可用 psql。打开 Terminal，使用你平常的连接设置运行 psql，并在整个 SQL 示例中保持同一会话开启。PostgreSQL 文档将 psql 描述为用于提交查询和查看结果的交互式终端。psql

先用 SHOW server_version; 检查服务器版本。本文检索到的当前文档指出，PostgreSQL 18 是当前版本。示例使用 PostgreSQL 12 或更高版本的语法；这是功能边界，并非建议使用旧版本。PostgreSQL 12 引入了 CTE 内联以及 MATERIALIZED 和 NOT MATERIALIZED 控制选项。WITH Queries、Release 12

在一个新会话中运行以下设置代码一次。它会创建 20,000 篇合成文章，并为按主题和 ID 查找建立索引。下文使用的候选条件会选出前 2,000 篇文章。

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

默认情况下，这些临时表会在整个会话期间保留。加载数据后，显式运行 ANALYZE：PostgreSQL 的 autovacuum 进程不会分析临时表。CREATE TABLE

现在记录基准结果。LATERAL 子查询会计算当前文章所属主题中的最高候选 ID。这里的“最高 ID”只是定义好的任务，不代表发布日期。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

显式使用 MATERIALIZED 可以明确本实验中的复用边界。物化的 CTE 可以只构建一次，但其使用方会反复读取存储的行。PostgreSQL 文档举例说明，被多次引用的物化 CTE 在搜索中间结果时会失去底层表索引带来的优势。公共表表达式的物化

本文检查过 SQL 示例，但未在 PostgreSQL 服务器上执行。合成数据的行数计算已另行核验；本文没有将任何执行时间或捕获的执行计划作为测量结果呈现。

阅读执行计划，定位重复工作

从生成候选集的分支开始，然后逐一追踪使用方。在嵌套循环中，辨认外层输入和内层查找。PostgreSQL 报告的 loops 是节点的执行次数；实际行数和节点耗时是每次执行的平均值。相关标量子查询版本可能会在 SubPlan 下显示重复工作，因此诊断时应追踪实际执行的节点，而不应要求计划中必须出现某个特定标签。使用 EXPLAIN

从实际执行计划中记录以下四项观察结果：

  1. 有多少行进入外层查找？
  2. 底层内层扫描执行了多少次？
  3. 该扫描是否读取候选行后又通过过滤器丢弃了大部分行？
  4. 外层行和耗时较大的内层工作之间是否有 Memoize 节点？

要区分不同层级。聚合节点每次执行可能只返回一个最大值，但其子节点可能扫描许多行来计算该值。因此，候选集生成器只运行一次，并不能说明候选集的使用成本很低。

对于这个特定样例，2,000 个候选项分布在 50 个主题中，每个主题有 40 篇文章。以下计算描述的是可能的工作模式，并非承诺实际执行计划输出或运行时间：

使用模式 示例性候选行检查次数
每篇外层文章都完整扫描一次候选集 2,000 × 2,000 = 4,000,000
每个主题完整扫描一次，之后的查找均由缓存处理 50 × 2,000 = 100,000
按主题将候选项分组一次 进入分组步骤的 2,000 行，另加后续连接工作

这种区别很重要。PostgreSQL 14 引入了内层嵌套循环结果的记忆化。当前文档将 Memoize 描述为缓存带参数的扫描结果，并允许缓存命中时跳过底层扫描；缓存条目也可能被逐出。在认定数千行外层数据对应数千次完整扫描之前，请检查缓存键以及报告的命中、未命中、逐出和溢出情况。Release 14、查询规划

同时，在最早出现明显差异的地方比较估算行数和实际行数。PostgreSQL 建议检查这些估算值，并提醒玩具表上的执行计划未必能推算出更大数据集上的表现。不要将父节点和子节点的耗时相加：它们描述的执行工作存在重叠。使用 EXPLAIN

选择与重复任务相匹配的改写方式

关键问题在于，每个使用方需要从候选集中完成多少工作。用下面的决策表选择第一个实验：

观察到的需求 首选实验 必须保留的条件
每个候选项都需要相同的主题级最大值 先分组，再连接 每个主题只生成一行汇总结果
每个使用方只需要源表中一小部分带索引的数据 尝试 NOT MATERIALIZED 保留完整的候选条件
多条语句需要显式带索引的候选关系 创建带索引的临时表 保持在同一会话中，并计入设置成本
重复参数已经产生大量缓存命中 改变查询结构前先比较总执行时间 确认底层扫描确实耗费较大

每个主题只计算一次结果

对于此任务，分组汇总能直接表达复用。完成设置后，在同一会话中运行以下代码：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

这种改写会为每个主题生成一个最大值，并将其附加到该主题中的每篇候选文章上。PostgreSQL 将 max 定义为非空输入值的最大值。样例中的主题列和 ID 列都没有空值，因此等价性很直接：每个候选项都属于一个包含它自身的分组。聚合函数

不使用 EXPLAIN 运行查询时，结果应有 2,000 行。例如，文章 1 属于主题 2，并获得 highest_id = 1951；主题 1 获得 highest_id = 2000。这些结果由样例生成器确定。

在推广此改写之前，请先确认结果含义。按额外列分组可能会让每个主题产生多行汇总结果，从而增加连接输出行数。如果允许主题 ID 为空，也需要重新审视相等比较的语义。如果任务要求最高 ID 对应文章的标题，还需要将最大值连接回候选文章；另外取标题的最大值会回答另一个问题。

这种改写允许采用不同的执行计划，但不强制使用哈希连接，也不保证提速。将实际的分组和连接工作与基准查询中内层节点的实际执行次数进行比较。

测试源表索引能否替代中间结果扫描

对于符合条件的 CTE，将完整基准查询中的 AS MATERIALIZED 替换为 AS NOT MATERIALIZED，然后重新运行。这允许 CTE 与源关系联合优化，有可能以源表查找代替对存储候选集的扫描。它也可能重复计算候选集。公共表表达式的物化

样例中的 (topic_id, id) 索引与前导主题列上的相等条件和 ID 列上的限制相匹配，因此值得测试。请检查实际计划是否使用该索引，以及扫描量有多大。PostgreSQL 文档指出，多列 B 树扫描可由前导列上的相等条件和下一列上的不等条件来限制。多列索引

在候选集定义中保留 id <= 2000。如果在全部 20,000 行源数据上查找最大值，答案就会改变。还要保留文档说明的适用边界：对于递归 CTE 和有副作用的 CTE，NOT MATERIALIZED 会被忽略。对于不符合条件的 CTE，请改用受支持的关系改写，或显式暂存其结果。SELECT

当多条语句需要复用时，暂存带索引的候选关系

CTE 仅属于一条语句。如果多条语句需要相同的候选行和显式索引，临时表可以提供不同的生命周期和索引选择。在完成原始设置后，运行以下替代方案：

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

临时表上的索引也具有临时性，且该关系仅在当前会话中有效。在评估暂存是否有帮助时，要计入建表、建索引和分析的成本。不要只拿最终查找与整个未暂存操作进行比较。CREATE TABLE

在 Mac 上使用 node-postgres 捕获相同实验

如需检查应用程序的查询路径，请使用当前受支持的 Node.js 版本，并通过 npm install pg 安装文档所述的软件包。在该项目中将以下完整程序保存为 diagnose.mjs，然后运行 node diagnose.mjs。程序会创建自己的合成输入，因此不依赖前面 psql 会话中的数据。node-postgres

该程序使用你现有的 PostgreSQL 连接环境。node-postgres 文档说明其使用与 psql 相同的连接环境变量，包括 PGHOST、PGPORT 和 PGDATABASE。连接

import pg from 'pg';
const { Client } = pg;
const client = new Client();

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log(version.rows[0]);

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

查询配置使用文档所述的 text 和 values 字段。添加 name 会在每个连接上创建一个预备语句；它不会缓存候选行，也不会消除内层的重复执行。查询

如果现有应用使用连接池，请在同一个签出的客户端上创建并使用临时表，然后在 finally 中释放该客户端。不同的 pool.query 调用可能会使用不同的客户端。同样，node-postgres 要求事务中的所有语句都使用同一个客户端实例。连接池、事务

对于行为会随参数值而变化的具名语句，请单独调查查询规划。PostgreSQL 可以选择通用计划或针对特定参数的自定义计划；文档所述的检查方法是对预备语句运行 EXPLAIN EXECUTE。因此，刚刚解释过的未具名查询可能会与已经建立的预备语句执行方式不同。PREPARE

判断重复工作是否确实减少

每次比较都使用相同的候选条件、选取列和输入值。先运行一轮，再重复运行可比的轮次，以观察缓存变化。记录服务器版本、候选项数量、内层扫描执行次数、缓存行为和总执行时间。PostgreSQL 17 改进了 CTE 规划中的列统计信息和排序顺序使用，因此比较不同机器上的结果时，要记录版本。Release 17

第二轮诊断可以使用 EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)，它会保留行数和语句执行时间，同时避免逐节点计时。父节点上的缓冲区总数包含子节点的活动，因此不要对整棵计划树求和。EXPLAIN ANALYZE 会执行查询，但不测量结果行传输到 Node.js 的耗时。EXPLAIN

将规划器开关视为实验。关闭 enable_nestloop 会降低嵌套循环的使用倾向，但无法彻底消除它；enable_material 也无法禁止所有物化。两个开关都不能证明候选集的使用已经变得高效。查询规划

最后选择一种既能保留所需行，又能减少完整任务中实测工作量的查询结构。对于这个主题级最大值问题，分组复用是最清晰的首选实验。如果带索引的查找经证实成本很低，就保留它；如果暂存带来的额外设置成本能在重复使用中得到回报，就采用显式暂存。

来源