PG JDBC官方文档中关于这部分的说明https://jdbc.postgresql.org/documentation/94/server-prepare.html

功能很明确避免SQL的反复解析（由于解析会消耗cpu资源，在操作频繁的数据库中，反复解析会给cpu造成压力）

为了说明过程，创建如下测试用例（数据库版本9.4.4)

点击(此处)折叠或打开

postgres=# create table test as select 111 a; /*测试表*/
postgres=> show log_parser_stats ; /*log_parser_stats参数为开启状态*/
log_parser_stats
------------------
on
postgres=# show log_planner_stats ; /*log_planner_stats参数为开启状态*/
log_planner_stats
-------------------
on

下面这段java代码，使用绑定变量的方式实现'selcet * from test where a=?',根据jdbc的说明，在连接串中设置prepareThreshold参数为3

意味着,在同一session中，同样的sql执行次数达到3次，下次执行将免去解析的过程，在程序中同样的sql执行4次，执行之后查看日志的情况

点击(此处)折叠或打开

import org.postgresql. * ;
import java.sql. * ;
public class TestDB_1 {
public TestDB_1() {
}
public static void main(String args[])
{
System.out.println( "this is a test" );
try
{
Class.forName( "org.postgresql.Driver" ).newInstance();
String url = "jdbc:postgresql://192.168.88.128:5432/postgres?prepareThreshold=3" ;
Connection conn = DriverManager.getConnection(url, "postgres" , "12345678" );
int foovalue = 111;
PreparedStatement st = conn.prepareStatement("SELECT * FROM test WHERE a = ?");
st.setInt(1, foovalue);
ResultSet rs = st.executeQuery();
ResultSet rs1 = st.executeQuery();
ResultSet rs2 = st.executeQuery();
ResultSet rs3 = st.executeQuery();
while (rs3.next())
{
System.out.println(rs3.getString( 1 ));
}
rs.close();
rs1.close();
rs2.close();
rs3.close();
st.close();
}
catch (Exception ee)
{
System.out.print(ee.getMessage());
}
}
}

将csv格式日志导入表中查看，java程序执行结果如下

点击(此处)折叠或打开

postgres=# select command_tag,message from postgres_log ;
command_tag | message
PARSE | PARSER STATISTICS
PARSE | PARSE ANALYSIS STATISTICS
PARSE | REWRITER STATISTICS
BIND | PLANNER STATISTICS
SELECT | execute : SELECT * FROM test WHERE a = $1
PARSE | PARSER STATISTICS
PARSE | PARSE ANALYSIS STATISTICS
PARSE | REWRITER STATISTICS
BIND | PLANNER STATISTICS
SELECT | execute : SELECT * FROM test WHERE a = $1
PARSE | PARSER STATISTICS
PARSE | PARSE ANALYSIS STATISTICS
PARSE | REWRITER STATISTICS
BIND | PLANNER STATISTICS
SELECT | execute S_1: SELECT * FROM test WHERE a = $1
BIND | PLANNER STATISTICS
SELECT | execute S_1: SELECT * FROM test WHERE a = $1

从日志中可以看出，postgres SQL解析日志中记录了三步PARSER STATISTICS->PARSE ANALYSIS STATISTICS ->REWRITER STATISTICS，当执行第四遍的时候已经忽略了解析的过程，之后会直接生成执行计划（有绑定变量，故第四次执行也要从新产生执行计划）

因为是浅谈，后面没有啦。。。

ITPub博客

浅谈PostgreSQL JDBC preparedstatement