为什么PostgreSQL JDBC预处理语句阈值默认设置为5? [英] Why is the PostgreSQL JDBC prepared statement threshold defaulted to 5?

问题描述

默认情况下，参数语句treshold设置为5，而不是1.即

By default, the parameter statement treshold is set to 5, instead of 1. That is,

((PGStatement) my_statement).getPrepareThreshold()

默认总是返回5.

这是什么原因?为什么我不需要在执行查询的前4次中使用服务器端的预处理语句?我不明白为什么我会将此值设置为非1的值，以及为什么默认情况下未将其设置为1.

What would be the reason for that? Why would I want not to have to use the server-side prepared statement for the first 4 times the query is executed? I fail to understand why I would ever set this to another value than 1 and why this isn't by default set to 1.

您能解释一下吗?非常感谢.

Can you explain? Thanks a lot.

推荐答案

服务器端准备好的语句消耗服务器端资源来存储该语句的执行计划.该阈值提供了一种启发式方法，该启发式方法可导致经常"准备实际使用的语句. 经常"的定义默认为5.

Server side prepared statements consume server side resources to store the execution plan for the statement. The threshold provides a heuristic that causes statements that are actually used "often" to be prepared. The definition of "often" defaults to 5.

请注意，服务器端预处理语句可能会导致执行计划不佳，因为它们不是基于在准备期间传递的参数.如果传递给已准备好的语句的参数在特定索引上的选择性不同(例如)，那么已准备好的语句的一般查询计划可能不是最佳的.再举一个例子，如果您遇到的情况是查询的执行量远远大于创建解释计划的成本，并且由于缺乏绑定参数而未正确设置解释计划，那么最好不使用服务器端准备好的语句.

Note that server side prepared statements can cause poor execution plans because they are not based on the parameters passed during the prepare. If the parameters passed to a prepared statement have a different selectivity on a particular index (for example), then the general query plan of the prepared statement may be suboptimal. As another example, if you have a situation where the execution of the query is much greater than the cost to create an explain plan, and the explain plan isn't properly set due to lack of bind parameters, you may be better off not using server side prepared statements.

当驾驶员达到阈值时，它将准备以下语句:

When the driver reaches the threshold, it will prepare the statement as follows:

    if (!oneShot)
    {
        // Generate a statement name to use.
        statementName = "S_" + (nextUniqueID++);

        // And prepare the new statement.
        // NB: Must clone the OID array, as it's a direct reference to
        // the SimpleParameterList's internal array that might be modified
        // under us.
        query.setStatementName(statementName);
        query.setStatementTypes((int[])typeOIDs.clone());
    }

语句名称是作为wire协议的一部分发送的，它告诉Postgres在服务器端进行准备.

The statement name is sent as part of the wire protocol, which tells Postgres to prepare it server side.

这篇关于为什么PostgreSQL JDBC预处理语句阈值默认设置为5?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！