0

I upgraded JDBC from postgresql-9.2-1000.jar(PostgreSQL 9.2.1) to postgresql-42.5.0.jar(PostgreSQL 14.6) and strange thing happened. With the same query, It takes slow after fourth, or ninth execution. (It doesn't happen with postgresql-9.2-1000.jar(PostgreSQL 9.2.1))

Example:

public class JDBCExample3 {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:postgresql://xxx.xxx.xxx.xxx:5432/testdb", "user",
                "password");
        String SQL = "select ...";

        for (int i = 0; i < 10; i++) {
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            //set parameters 
            pstmt.setString(0, "122344acbc");
            // ... 
            
            // It's not slow with i from 0 to 8.
            ResultSet rs = pstmt.executeQuery(); //When i = 9 (after ninth execution), It takes long time here.  slow!!!
            while (rs.next()) {
                //get result
                rs.getString("name");
                //...
            }

            System.out.println("loop:" + i);
            
            //close
            rs.close();
            rs = null;
            pstmt.close();
            pstmt = null;
        }
        conn.close();
        System.out.println("end");
    }
}

I'm trying to fix it. What should I pay attention to about postgresql-42.5.0.jar(PostgreSQL 14.6) here?

I expected the "pstmt.executeQuery()" took the same time with all execution.

Thttp
  • 1
  • 2
    Try to disable [server side prepared statements](https://jdbc.postgresql.org/documentation/server-prepare/#server-prepared-statements) by setting prepareThreshold to 0 –  Apr 14 '23 at 08:13
  • It works! Many thanks! I wonder that... Any project (using PreparedStatement) that also requires this setting prepareThreshold =0 (default prepareThreshold = 5)? And what different with setting plan_cache_mode = force_custom_plan? – Thttp Apr 14 '23 at 08:27
  • 2
    Look [here](https://stackoverflow.com/questions/38188248/after-9-queries-to-database-the-time-to-perform-the-same-query-is-multiplied-by/38188817?r=SearchResults&s=4%7C40.3890#38188817) for a detailed explanation. – Laurenz Albe Apr 14 '23 at 08:37

0 Answers0