Oracle vs PostgreSQL Develop（16） - Prepared Statement

对于除绑定变量外其余相同的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) (actual 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) (actual 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;PREPARETime: 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) (actual 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) (actual 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) (actual 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
下面是测试代码

/* * 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: 1Execution: 2, Used server side: false, Result: 2Execution: 3, Used server side: false, Result: 3Execution: 4, Used server side: false, Result: 4Execution: 5, Used server side: true, Result: 5Execution: 6, Used server side: true, Result: 6Execution: 7, Used server side: true, Result: 7Execution: 8, Used server side: true, Result: 8Execution: 9, Used server side: true, Result: 9Execution: 10, Used server side: true, Result: 10

5次后开始使用服务器端的Prepared Statement.

参考资料
Server Prepared Statements