对于除绑定变量外其余相同的SQL语句,PostgreSQL提供了Prepared Statement用于缓存Plan,以达到Oracle中cursor_sharing=force的目的. **PSQL** 通过prepare语句,可为SQL生成Prepared Statement,减少Plan的时间 ``` [local]:5432 pg12@testdb=# explain (analyze,verbose) select * from t_prewarm where id = 1; QUERY PLAN -------------------------------------------------------------------------------------------- Index Scan using idx_t_prewarm_id on public.t_prewarm (cost=0.42..8.44 rows=1 width=13) (a ctual time=0.125..0.127 rows=1 loops=1) Output: id, c1 Index Cond: (t_prewarm.id = 1) Planning Time: 0.613 ms Execution Time: 0.181 ms (5 rows) Time: 2.021 ms [local]:5432 pg12@testdb=# explain (analyze,verbose) select * from t_prewarm where id = 1; QUERY PLAN -------------------------------------------------------------------------------------------- Index Scan using idx_t_prewarm_id on public.t_prewarm (cost=0.42..8.44 rows=1 width=13) (a ctual time=0.184..0.193 rows=1 loops=1) Output: id, c1 Index Cond: (t_prewarm.id = 1) Planning Time: 0.520 ms Execution Time: 0.276 ms (5 rows) ``` 不使用prepare,可看到每次的Planning时间比Execution时间还要长 ``` [local]:5432 pg12@testdb=# prepare p(int) as select * from t_prewarm where id=$1; PREPARE Time: 1.000 ms [local]:5432 pg12@testdb=# explain (analyze,verbose) execute p(2); QUERY PLAN -------------------------------------------------------------------------------------------- Index Scan using idx_t_prewarm_id on public.t_prewarm (cost=0.42..8.44 rows=1 width=13) (a ctual time=0.037..0.039 rows=1 loops=1) Output: id, c1 Index Cond: (t_prewarm.id = 2) Planning Time: 0.323 ms Execution Time: 0.076 ms (5 rows) Time: 1.223 ms [local]:5432 pg12@testdb=# explain (analyze,verbose) execute p(3); QUERY PLAN -------------------------------------------------------------------------------------------- ---------------------------------------- Index Scan using idx_t_prewarm_id on public.t_prewarm (cost=0.42..8.44 rows=1 width=13) (a ctual time=0.077..0.081 rows=1 loops=1) Output: id, c1 Index Cond: (t_prewarm.id = $1) Planning Time: 0.042 ms Execution Time: 0.174 ms (5 rows) Time: 1.711 ms [local]:5432 pg12@testdb=# explain (analyze,verbose) execute p(4); QUERY PLAN -------------------------------------------------------------------------------------------- ---------------------------------------- Index Scan using idx_t_prewarm_id on public.t_prewarm (cost=0.42..8.44 rows=1 width=13) (a ctual time=0.042..0.044 rows=1 loops=1) Output: id, c1 Index Cond: (t_prewarm.id = $1) Planning Time: 0.019 ms Execution Time: 0.084 ms (5 rows) ``` 使用prepare,可看到Planning时间明显降低 **JDBC Driver** 下面是测试代码 ```Java /* * TestPlanCache * * Copyright (C) 2004-2016, Denis Lussier * Copyright (C) 2016, Jan Wieck * */ package testPG; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class TestPGPlanCache { public static void main(String[] args) { Connection conn = null; Statement stmt = null; String rLine = null; StringBuffer sql = new StringBuffer(); try { Properties ini = new Properties(); // ini.load(new FileInputStream(System.getProperty("prop"))); // Register jdbcDriver Class.forName("org.postgresql.Driver"); // make connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.26.28:5432/testdb", "pg12", "pg12"); conn.setAutoCommit(true); PreparedStatement pstmt = conn.prepareStatement("SELECT * from t_prewarm where id = ?"); // cast to the pg extension interface org.postgresql.PGStatement pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class); // on the third execution start using server side statements // pgstmt.setPrepareThreshold(3); for (int i = 1; i <= 10; i++) { pstmt.setInt(1, i); boolean usingServerPrepare = pgstmt.isUseServerPrepare(); ResultSet rs = pstmt.executeQuery(); rs.next(); System.out.println( "Execution: " + i + ", Used server side: " + usingServerPrepare + ", Result: " + rs.getInt(1)); rs.close(); } pstmt.close(); } catch (SQLException se) { System.out.println(se.getMessage()); } catch (Exception e) { e.printStackTrace(); // exit Cleanly } finally { try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } // end finally } // end try } // end main } // end ExecJDBC Class ``` 输出为 ``` Execution: 1, Used server side: false, Result: 1 Execution: 2, Used server side: false, Result: 2 Execution: 3, Used server side: false, Result: 3 Execution: 4, Used server side: false, Result: 4 Execution: 5, Used server side: true, Result: 5 Execution: 6, Used server side: true, Result: 6 Execution: 7, Used server side: true, Result: 7 Execution: 8, Used server side: true, Result: 8 Execution: 9, Used server side: true, Result: 9 Execution: 10, Used server side: true, Result: 10 ``` 5次后开始使用服务器端的Prepared Statement. **参考资料** [Server Prepared Statements](https://jdbc.postgresql.org/documentation/head/server-prepare.html)