PostgreSQL服务器端准备语句的寿命是多长？ [英] What's the life span of a PostgreSQL server-side prepared statement

问题描述

根据 PostgreSQL文档，准备好的语句被绑定到数据库会话/ connection：

PREPARE创建一个准备语句。预准备语句是一个
服务器端对象，可用于优化性能。当执行
PREPARE语句时，将解析指定的语句，分析并重写
。当一个EXECUTE命令随后被发出
时，计划并执行预备语句。

预编译语句只在当前数据库的持续时间内有效
会话。当会话结束时，准备的语句被忘记，
所以它必须在再次使用之前重新创建。

， Markus Winand（SQL Performance Explained作者）说，：

PostgreSQL没有共享的查询计划缓存，但对于预准备的语句，它有一个
可选的查询计划缓存。这意味着
开发人员可以选择使用带有或不带
缓存查询计划的预准备语句。但请注意，当
准备语句关闭时，缓存被删除。

哪一个是真的？

只要数据库连接已打开，准备好的语句就会存在，因此在使用连接池时，只要池未明确关闭物理

解决方案

，则会清除服务器端预处理语句。 >所以，你的问题终于归结为如何 java.sql.PreparedStatement 播放与PostgreSQL。

这里的答案取决于你使用的JDBC驱动程序。

TL; DR ：在现代驱动程序服务器准备语句中，直到连接消失或直到语句被另一个驱逐（常规LRU逐出）。 >

注意：PostgreSQL服务器不能跨数据库连接共享预准备语句，因此最好的JDBC驱动程序可以保持计划在每个连接中缓存。

注意：JDBC规范强制使用？，？用于绑定占位符，而服务器需要 $ 1，$ 2 因此JDBC驱动程序也缓存所谓的解析的SQL文本。

有两个众所周知的JDBC驱动程序：pgjdbc和pgjdbc-ng

pgjdbc

https：/ /github.com/pgjdbc/pgjdbc

由于 pgjdbc 9.4-1202 当使用 PreparedStatement 时，它会自动缓存服务器端计划。
注意：即使您 close（） PreparedStatement ，语句也会被缓存。
为了得到服务器端的准备，你需要执行查询5次（可以通过 prepareThreshold 配置）。

目前，缓存是每个连接实现的。默认情况下，pgjdbc缓存256（ preparedStatementCacheQueries ）查询和 preparedStatementCacheSizeMiB 查询。这是一个保守的设置，因此您可能需要调整它。有关属性的说明，请参见文档。
缓存包含解析的和服务器准备的语句。​​

github问题： https://github.com/pgjdbc/pgjdbc/pull/319

pgjdbc-ng

https://github.com/impossibl/pgjdbc-ng

我没有进入pgjdbc-ng，但它看起来像是解析（默认缓存大小是 250 查询）和服务器准备（默认缓存大小为 50 查询）。服务器端预处理语句的支持已于2014年2月24日上线，因此如果您使用某些最新版本，可以获取语句缓存。

注意：如果您不小心使用非常因为pgjdbc-ng不能根据保留的字节数逐出条目。

由于长整型查询，您可以按 OutOfMemory 缓存是每个连接，因此即使你关闭语句，它也是透明的。

我不能说很多关于pgjdbc-ng的性能，虽然从上次我试图抛出

github问题： https://github.com/impossibl/pgjdbc-ng/pull/69

服务器准备的计划

PostgreSQL有 PREPARE 和 DEALLOCATE 命令， code> EXEC 。它优化了两件事：

1. 使用 PREPARE d语句时（换句话说，服务器准备的），客户端不必一次又一次发送查询文本。它只是发送一个简短的查询名称和绑定变量的值。


2. 自从9.2以来，数据库仍然尝试重新安装查询的前几个执行。它这样做以尝试如果查询需要多个计划或如果通用计划足够好。最终（如果查询没有参数，则立即），数据库可能会切换一个通用计划。

换句话说， PreparedStatement

更多信息： http://blog.endpoint.com/2014/04/custom-plans-prepared-statements-in.html

在PL / pgSQL中准备的语句

按照文档，PostgreSQL caches 计划。这发生在几次执行之后（3或5，我不记得确切的阈值），所以在创建存储过程后，它可能有点慢，但是它会切换到缓存计划（如果数据库同意使用通用计划对于特定的查询）。

换句话说，为了实现缓存执行计划，您需要使用最新的JDBC驱动程序，你对存储过程的查询。
在每次执行时将调用过程，但调用本身通常比构成过程的查询短得多。

According to the PostgreSQL documentation, a prepared statement is bound to a database session/connection:

PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed.

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again.

But then, Markus Winand (author of SQL Performance Explained) says that:

PostgreSQL does not have a shared query plan cache, but it has an optional query plan cache for prepared statements. That means that the developer has the choice to use a prepared statement with or without cached query plan. But note that the cache is dropped when the prepared statement is closed.

Which one is true?

Does the prepared statement live as long as the database connection is open, so when using a connection pool this can live as long as the pool doesn't explicitly closes the physical connection or does the server-side prepared statement is wiped out once the JDBC PreparedStatement is closed.

解决方案

So, your question finally boils down to "how java.sql.PreparedStatement plays with PostgreSQL". See answer on "how this plays with server-prepared plans" in the end.

Here's the answer: that depends on the JDBC driver you use.

TL;DR: in modern drivers server-prepared statement lives until connection dies or until the statement is evicted by another one (regular LRU eviction).

Note: PostgreSQL server cannot share prepared statement across database connections, thus the best JDBC driver can do is to keep plan cached in each connection.

Note: JDBC spec mandates usage of ?, ? for bind placeholders, while server wants $1, $2 thus JDBC drivers cache so-called parsed SQL texts as well.

There are two well-known JDBC drivers: pgjdbc and pgjdbc-ng

pgjdbc

https://github.com/pgjdbc/pgjdbc

Since pgjdbc 9.4-1202 it automatically caches server-side plans when using PreparedStatement. Note: the statements are cached even if you close() the PreparedStatement. In order to get to server-side prepare, you need to execute the query 5 times (that can be configured via prepareThreshold).

Currently, the cache is implemented per-connection. By default pgjdbc caches 256 (preparedStatementCacheQueries) queries and up to preparedStatementCacheSizeMiB of queries. This is a conservative setting, so you might want adjusting it. See documentation for the description of properties. The cache includes both parsed and server-prepared statements.

github issue: https://github.com/pgjdbc/pgjdbc/pull/319

pgjdbc-ng

https://github.com/impossibl/pgjdbc-ng

I'm not into pgjdbc-ng, however it looks like it does both parsing (default cache size is 250 queries) and server-preparing (default cache size is 50 queries). The support of server-side prepared statements landed on 24 Feb 2014, so if you use somewhat recent version, you can get statement caching.

Note: if you accidentally use very long queries, you can hit OutOfMemory since pgjdbc-ng cannot evict entries based on the number of retained bytes.

The cache is per-connection, thus it is transparently used even if you close statements.

I cannot say much about pgjdbc-ng performance though since last time I tried to throw jmh at it it failed with random exceptions.

github issue: https://github.com/impossibl/pgjdbc-ng/pull/69

Server-prepared plans

PostgreSQL has PREPARE and DEALLOCATE commands to reference the statement when sending EXEC over the wire. It optimizes two things:

1. When using PREPAREd statement (in other words, server-prepared one), client does not have to send query text again and again. It just sends a short query name and the values for bind variables.
2. Since 9.2, database still tries to replan first few executions of a query. It does so to try if the query needs multiple plans or if generic plan is good enough. Eventually (immediately if the query has no parameters), the database might switch to a generic plan.

In other words, PreparedStatement optimizes both query parsing at JDBC side and query planning at database side.

More info here: http://blog.endpoint.com/2014/04/custom-plans-prepared-statements-in.html

Prepared statements in PL/pgSQL

As per documentation, PostgreSQL caches plans for queries used in PL/pgSQL. This happens after a few executions (3 or 5, I do not remember the exact threshold), so after you create stored procedure it might be a bit slow, however then it will switch to cached plans (provided the database agrees to use generic plan for a particular query).

In other words in order to achieve "cached execution plans", you either need to use up to date JDBC driver, or you can wrap all your queries into stored procedures. The call to procedure will replan at each execute, however the call itself is typically much shorter than queries that compose the procedure.

这篇关于PostgreSQL服务器端准备语句的寿命是多长？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！