Reusing Postgres prepared statement

Question

I was looking at this question Reusing a PreparedStatement multiple times

Let's say I have two versions of code. The first closes the PreparedStatement after each use, inside the for loop.

connection.autoCommit(false)    
for (int i=0; i<1000; i++) {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, someValue);
        preparedStatement.executeQuery();
        preparedStatement.close();    //  Close after each use.
    }
    connection.commit();
    connection.close();

In this second example, the PreparedStatement remains open for repeated use, to be closed later after the for loop.

connection.autoCommit(false)
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i=0; i<1000; i++) {
    preparedStatement.clearParameters();
    preparedStatement.setObject(1, someValue);
    preparedStatement.executeQuery();
}
preparedStatement.close();           //  Close after repeated uses.
connection.commit();
connection.close();

As you can see I do create and close PS in loop or reuse the same PS. I do use postgres and according to documentation

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten

As I understand if I use postgres then two examples above will be handled in the same way because it's executed in the same transaction. So for example in the first code sample with new statement inside the loop , postgres will create single planner for statement and even if we close statement and create new one in loop postgres will reuse cached one because it happens in the same session(transaction) and this cached planner will be deleted only when transaction will be committed(connection.commit()). Am I right ?

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

The prepared statement duration lasts for the duration of the database session which is different from the transaction (the session ends when the client disconnects whereas the transaction ends -usually- with COMMIT or ROLLBACK).

Whether a single plan or several plans are generated depends at least on the following (it's PREPARE STATEMENT that generates the plan):

A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)

By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.

[quoting Postgres SQL PREPARE command]

If the JDBC `PreparedStatement` object goes out-of-scope (and heads for garbage collection), does Postgres continue to retain the prepared statement with its plan(s) until the session/connection ends? If so, is there any way back to that prepared statement on the Postgres side? [perhaps my asking is too much of a tangent] — Basil Bourque, May 05 '20 at 20:53

Reusing Postgres prepared statement

1 Answers1