Oracle vs PostgreSQL Develop (16)-Prepared Statement

For the same SQL statements except binding variables, PostgreSQL provides Prepared Statement for caching Plan to achieve the purpose of cursor_sharing=force in Oracle.

PSQL

Through the prepare statement, you can generate Prepared Statement for SQL, reducing the time of 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)

If you do not use prepare, you can see that each time the Planning time is longer than the Execution time

[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)

Using prepare, you can see that the Planning time is significantly reduced.

JDBC Driver

Here is the test code

/ * * 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