[其他] Jovis深度可视化：揭秘PostgreSQL查询优化器的内部决策过程

传统上，当我们需要分析 PostgreSQL 中一个 SQL 的性能问题时，通常只能依赖 EXPLAIN 或 EXPLAIN ANALYZE 查看最终选定的执行计划。更进一步，PEV、pgAdmin 等工具也仅仅是将这个最终的执行计划树可视化出来。

然而，一个关键的信息黑盒始终存在：

• 优化器背后究竟考虑了哪些候选路径、连接顺序和算子组合？
• 它为何最终选择这个执行计划？哪些计划被淘汰了，其成本估算分别是多少？
• 表中的行数（rows）是如何被预估出来的？

Jovis 的目标正是填补这一空白。它是一个基于论文《Jovis: A Visualization Tool for PostgreSQL Query Optimizer》的原型工具，旨在完整记录并可视化 PostgreSQL 标准动态规划（DP）优化器与遗传查询优化器（GEQO）的内部决策过程（包括搜索空间、备选路径及其成本），同时允许用户通过提示（hint）或配置参与优化过程。

Jovis 的核心优势：从结果到过程

现有工具仅展示最终的“计划树”（Plan Tree），而 Jovis 呈现的是优化器的“决策过程”：

• 对于标准优化器：展示所有路径、连接顺序、算子组合的有向无环图（DAG）及其成本演化过程。
• 对于GEQO：展示每一代种群中各个基因（代表连接序列）的成本，以及整个进化过程。

它能清晰地解释“为什么选择这个计划”，并提供各备选计划的成本对比。它同时覆盖了 DP 和 GEQO 两种优化策略。 此外，Jovis 集成了 pg_hint_plan，允许用户通过 /*+ ... */ 语法为特定算子或连接顺序添加提示，并直观地观察这些提示对搜索空间和成本估算的影响，实现用户可引导的优化。

架构概览：轻量补丁与可视化前端

Jovis 的架构主要由三部分组成：

1. 打过补丁的 PostgreSQL：此补丁在优化器内部增加了详尽的日志输出，并将日志写入文本文件。论文强调这是一个轻量级、非侵入性的补丁，对原有优化逻辑干扰极小。日志内容包括：
   • 标准优化器：路径枚举信息、每个路径的成本、连接序列等。
   • GEQO：每一代的基因（连接序列）、交叉操作以及各基因的成本。
2. 后端服务（Python）：该服务负责接收前端提交的 SQL，调用打过补丁的 PostgreSQL 执行查询（包括完整的优化和执行过程），并生成优化器日志。随后，它会解析日志，判断使用的是标准优化器还是 GEQO，提取路径列表、连接序列、成本信息等，并将其转换为结构化的 JSON 数据，连同查询结果一并发送给前端。
3. 前端界面（React + D3.js）：提供 SQL 编辑、预设/历史查询管理等 UI。其核心是两大可视化视图：
   • 标准优化器规划视图（DAG 图）。
   • GEQO 规划视图（基因热力图、成本演化曲线、成本分解图）。
     此外，它还包含一个 EXPLAIN 视图，用于将最终的执行计划绘制成树状图，功能类似于 PEV2，是进行 SQL性能分析 的得力助手。

实践演示：洞察优化器的“思考”

让我们通过一个具体 SQL 示例来体验 Jovis 的能力。

explain
SELECT  c.customer_id,
        count(*) AS cnt,
        sum(o.amount) AS total_amount
FROM (
    SELECT * FROM customer
    WHERE credit_score > 700
) c
JOIN (
    SELECT * FROM orders
    WHERE status = 0
) o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY total_amount DESC
LIMIT 100;

在 Jovis 的 EXPLAIN 视图中，我们可以看到与 PEV 类似的树状执行计划。而在左侧的“Query Planning”视图中，则可以观察到优化器实际评估过的所有连接方式（如 Nested Loop、MergeJoin），而不仅仅是最终选择的 Hash Join。毫无疑问，未被选中的连接方式因其估算成本更高而被淘汰。我们可以通过“PLAY”按钮动态观察优化器进行路径选择的整个过程。

很多时候，优化器可能会选择一个并非最优的执行路径，而我们通常没有太好的办法去深入理解其原因，只能通过 HINT 或会话级参数进行手动调整。借助 Jovis，我们可以深入其内部决策逻辑。点击具体的算子（如 Nested Loop），可以查看其详细的成本计算过程。例如，在本例中 Nested Loop 的估算成本显示为 58142。

我们可以在数据库中验证这一点。通过禁用 Hash Join (set enable_hashjoin to off;)，再次执行 EXPLAIN，可以看到优化器转而选择了 Nested Loop，其总成本确实为 58143.01，与 Jovis 展示的估算值基本一致。

->  Nested Loop  (cost=0.30..58143.01 rows=214316 width=10)

接下来演示 GEQO 的优化过程。我们使用一个涉及多表自连接的查询来触发 GEQO：

WITH x AS( SELECT * FROM generate_series(1, 1000) AS id)
SELECT *
FROM x AS a
     JOIN x AS b ON (a.id = b.id)
     JOIN x AS c ON (b.id = c.id)
     JOIN x AS d ON (c.id = d.id)
     JOIN x AS e ON (d.id = e.id)
     JOIN x AS f ON (e.id = f.id)

在 Jovis 的 GEQO 视图中，我们可以看到遗传算法的优化过程。由于涉及大量连接顺序的“穷举”搜索，生成可视化结果可能需要一些时间。

需要指出的是，演示过程中可能会遇到一些小 BUG，例如渲染失败或算子显示为“Undefined”。这是因为当前公开发布的 GitHub 仓库仅是论文 Demo 代码的一个极简子集，并未包含论文中描述的全部高级功能（如完整的 GEQO 进化过程动画、DAG 搜索空间动画、成本分解可视化等）。论文作者也明确说明这是一个用于演示的研究原型系统。

总结与展望

尽管当前开源版本的 Jovis 仍是论文原型的一部分，距离完整的研究系统尚有一定差距，但瑕不掩瑜。即使是这个“轻量级 Demo”，也已足够让我们窥见 PostgreSQL 查询优化 器强大内部机制的冰山一角。

它使我们得以从传统 EXPLAIN 的“结果视角”，跨越到理解优化器内部“思考路径”的“过程视角”。这不仅让执行计划背后的推导逻辑变得前所未有的直观，也为数据库教学、性能深度调优乃至未来探索更智能的优化器形式奠定了宝贵的基础。

随着研究团队后续可能逐步开放更多组件与可视化能力，Jovis 有望成为数据库开发者、DBA 和研究者深入理解 PostgreSQL 优化器原理、探索其性能潜力的重要工具，值得持续关注并在实践中深入研究。

参考资料

• Jovis: A Visualization Tool for PostgreSQL Query Optimizer: https://dl.acm.org/doi/10.1145/3733723.3733744