I upgraded JDBC driver to postgresql, strange thing happened

171 Views Asked by At

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.

0

There are 0 best solutions below