Why is the default PostgreSQL JDBC instruction threshold set to 5?

Question

By default, the expression for the treshold parameter is set to 5, not 1. That is

((PGStatement) my_statement).getPrepareThreshold() 

always returns 5 by default.

What is the reason for this? Why don't I want to use a server-side pre-made statement for the first four times when the request is executed? I do not understand why I would ever set this to a value other than 1, and why this is not set to 1 by default.

Can you explain? Many thanks.

Answer 1

Trained server-side statements consume server-side resources to store the execution plan for the agent. The threshold provides a heuristic that causes statements that are actually used “often” for preparation. The definition of “often” defaults to 5.

Please note that prepared statements on the server side may cause poor execution plans, since they are not based on the parameters passed during preparation. If the parameters passed to the prepared operator have different selectivity for a specific index (for example), then the general query plan of the prepared operator may be suboptimal. As another example, if you have a situation where the execution of the request is much more than the cost of creating an explanation plan, and the explanation plan is incorrectly set due to the lack of binding parameters, you might be better off not using the server side.

When the driver reaches the threshold, he will prepare the operator 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 a direct reference to // the SimpleParameterList internal array that might be modified // under us. query.setStatementName(statementName); query.setStatementTypes((int[])typeOIDs.clone()); } 

The operator name is sent as part of the wired protocol that tells Postgres to prepare it on the server side.