對於除繫結變數外其餘相同的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)

Oracle vs PostgreSQL Develop（16） - Prepared Statement

相關文章