如何获取带参数的PostgreSQL执行计划？

Question

我的应用程序有大量的查询，我想获取其中一些查询的执行计划。大多数（如果不是全部）查询都有多个参数，我找不到如何在PostgreSQL中获取任何非平凡查询的执行计划。

实际情况要复杂得多，但这里有一个简单的代表性查询案例（仅有一个参数以简化问题）：

Connection conn = DriverManager.getConnection("...", "...", "...");

PreparedStatement ps1 = conn.prepareStatement(
  "prepare x as select * from documents where content = $1");
ps1.execute();

PreparedStatement ps2 = conn.prepareStatement(
  "explain (format json) execute x (?)");
ps2.setString(1, "Very long content here..."); // Binds the parameter
ResultSet rs = ps2.executeQuery(); // Error here!

while (rs.next()) {
  System.out.println(rs.getString(1));
}

当我运行这个程序时，会出现以下错误：

ERROR: 没有参数 $1 Position: 34

如果我 硬编码 参数（例如将 $1 替换为 'a'），则一切正常并且我得到了一个执行计划。但是，如果我尝试使用 JDBC 参数，则无法工作。在我的用例中，硬编码参数并不现实，因为它可能是大量的参数或者无法正确呈现为字符串（例如浮点值）。

我还尝试使用 null 而不是 ?，程序不会崩溃，但返回的执行计划是错误的；似乎它以某种方式绕过了逻辑并返回了与现实完全不同的结果。

我做错了什么？

- Joe DiNottra

---

不幸的是，那个解决方案适用于不同的用例。主要答案似乎是在第二次调用中硬编码参数。这对我的用例来说并不现实。它可能是一个巨大的参数，或者可能无法正确呈现为字符串（例如浮点值）。 - Joe DiNottra

我不使用Java，但是稍微查找了一下，发现这个服务器准备 服务器准备语句动机。看起来你可能需要执行以下操作：ps1 = conn.prepareStatement( "select * from documents where content = ?"); - Adrian Klaver

这就是我在第二个调用中所做的。第一个必须使用 $1 来创建一个 PostgreSQL 过程。我不明白为什么问题被关闭了。相关答案是针对另一个问题的。 - Joe DiNottra

1. 我投票支持重新开放这个问题，因为它是不同的。
2. 那不是你正在做的事情，你正在使用 prepare 和它的占位符 $1 在你的查询版本中。如果我正确理解文档，这个问题会被 PreparedStatement 自动处理。

- Adrian Klaver

Answer 1

您不需要用绑定变量值替换$1，但显然不能在explain execute语句中使用绑定变量 - 必须在此处声明。

因此，这个序列可以正常工作（伪代码）

prep = con.prepareStatement("prepare x(integer) as select id, pad from jdbn.document where id = $1")
prep.execute();

stmt = con.prepareStatement("explain execute x (42)")
rs = stmt.executeQuery()

如在先前标记为重复的问题中所述，您在最初的几次执行中会得到一个自定义计划（即您在条件中看到变量，这里是id = 42），最终得到一个通用计划（即带有谓词id = $1）

您使用null的技巧失败了，因为PostgreSql知道id = null不返回任何内容（我使用模糊的表述避免既不是true也不是false），并生成一个虚拟计划，例如One-Time Filter: false

您可以通过plan_cache_mode参数强制生成一个通用计划

con.createStatement().execute("set plan_cache_mode = force_generic_plan")

在这种情况下，第一个解释的计划是通用的。似乎在这种设置下，null参数不会导致虚拟计划（但我不确定是否还有其他注意事项）。

因此，我建议您始终定义变量prepare x(integer) ...的数据类型，但我不确定并且没有经验，如果您可以通过force_generic_plan和传递null（类似于Oracle功能）获得有意义的执行计划，或者您真正必须传递表示示例值或参数。

最后说明，所有讨论都集中在设计良好的预处理语句上，即传递的任何可能值都将导致单个相同的执行计划。对于其他语句，这是没有意义的，因为没有单个执行计划。

- Marmite Bomber

---

感谢您的回答。我认为我无法执行explain execute x(42)，因为这需要将参数（在本例中为42）呈现为字符串，并将它们连接到SQL查询中。在这种情况下，某些参数可能非常大，或者可能不容易转换为字符串，例如浮点数。此外，我不喜欢为SQL注入打开窗口。 - Joe DiNottra

抱歉让@JoeDiNottra感到困惑，我并不是在建议你要这样做，我只是展示了在JDBC中如何工作。我还尝试指向force_generic_plan和传递null的组合，在你的情况下我会尝试研究它并检查它是否提供了有价值的结果。 - Marmite Bomber

当你说“或者如果你真的必须传递表示样本值(s)”，那让我思考了一下。也许我可以不传递“非常长的字符串”，而是传递一个硬编码的“a”值。也许这已经足够得到一个有效的计划了。如果是这种情况，我只需要确保每个单独的PostgreSQL数据类型都有一个硬编码的值，那就完成了！ - Joe DiNottra

Answer 2

你可以尝试这行代码

PreparedStatement ps1 = conn.prepareStatement(
  "prepare x as select * from documents where content = '"+ 1 +"'");
ps1.execute();

替代

   PreparedStatement ps1 = conn.prepareStatement(
      "prepare x as select * from documents where content = $1");
    ps1.execute();

- Hacı Celal Aygar