NAME
PREPARE - 创建一个准备好的查询
SYNOPSIS
PREPARE plan_name [ (datatype [, ...] ) ] AS statement
DESCRIPTION 描述
PREPARE 创建一个已准备好的查询。 一个已准备好的查询是服务器端的对象，可以用于优化性能。 在执行 PREPARE 语句的时候，指定的查询被分析，重写以及规划。 当随后发出 EXECUTE 语句的时候， 已准备好的查询就只需要执行了。因此，分析，重写，以及规划阶段都只执行一次，而不是每次查询执行的时候都要执行一次。
准备好的查询可以接受参数：在它执行的时候替换到查询中的数值。 要给一个准备好的查询声明参数，我们需要在 PREPARE 语句里包含一个数据类型的列表。在查询本身里，你可以按照位置来引用这些参数， 比如 $1，$2，等。 在执行查询的时候，在 EXECUTE 语句里为这些参数声明实际的数值。 参考 EXECUTE [execute(7)] 获取更多信息。
准备好的查询是在本地存储的（在当前后端里），并且只是在当前数据库会话的过程中存在。 如果客户端退出，那么准备好的查询就会被遗忘，因此我们必须在被重新使用之前重新创建。 这也意味着一个准备好的查询不能被多个同时的数据库客户端使用； 但是，每个客户端可以创建它们自己的已准备好的查询来使用。
如果一个会话准备用于执行大量类似的查询，那么已准备好的查询可以获得***限度的性能优势。 如果查询非常复杂，需要复杂的规划或者重写，那么性能差距将非常明显。 比如，如果查询设计许多表的连接，或者有多种规则要求应用。如果查询的规划和重写相对简单， 而执行起来开销相当大，那么已准备好的查询的性能优势就不那么明显。
PARAMETERS 参数
- plan_name
给予这个特定的已准备好查询的任意名字。它必须在一个会话中是唯一的， 并且用于执行或者删除一个前面准备好的查询。- datatype
已准备好查询的某个参数的数据类型。要在已准备好查询内部引用这个参数，使用 $1，$2，等。- statement
任何 SELECT, INSERT, UPDATE, 或 DELETE 语句。
NOTES 注意
在一些情况下，PostgreSQL 为一个已准备好的查询生成的查询规划可能还不如按照普通方法提交并执行的查询生成的规划好。 这是因为该查询在被规划的时候（也是优化器视图判断***查询规划的时候）， 在查询中声明的任何参数的实际数值都还不可见。 PostgreSQL 在表中收集数据分布的统计， 而且可以利用查询中的常量来猜测执行查询的可能结果。 因为这些数据在准备哪种带参数的查询的规划的时候还不可得， 所以，选出来得规划可能是次好的。 要检查 PostgreSQL 为已准备好的查询选取的查询计划， 使用 EXPLAIN EXECUTE 。
有关查询规划和 PostgreSQL 为查询优化的目的收集的统计的更多信息， 参阅 ANALYZE [analyze(7)] 文档。
#p#
NAME
PREPARE - prepare a statement for execution
SYNOPSIS
PREPARE plan_name [ (datatype [, ...] ) ] AS statement
DESCRIPTION
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, rewritten, and planned. When an EXECUTE command is subsequently issued, the prepared statement need only be executed. Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the statement is executed.
Prepared statements can take parameters: values that are substituted into the statement when it is executed. To include parameters in a prepared statement, supply a list of data types in the PREPARE statement, and, in the statement to be prepared itself, refer to the parameters by position using $1, $2, etc. When executing the statement, specify the actual values for these parameters in the EXECUTE statement. Refer to EXECUTE [execute(7)] for more information about that.
Prepared statements are only stored in and for the duration of the current database session. When the session ends, the prepared statement is forgotten, and so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use.
Prepared statements have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, for example, if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.
PARAMETERS
- plan_name
- An arbitrary name given to this particular prepared statement. It must be unique within a single session and is subsequently used to execute or deallocate a previously prepared statement.
- datatype
- The data type of a parameter to the prepared statement. To refer to the parameters in the prepared statement itself, use $1, $2, etc.
- statement
- Any SELECT, INSERT, UPDATE, or DELETE statement.
NOTES
In some situations, the query plan produced by for a prepared statement may be inferior to the plan produced if the statement were submitted and executed normally. This is because when the statement is planned and the planner attempts to determine the optimal query plan, the actual values of any parameters specified in the statement are unavailable. PostgreSQL collects statistics on the distribution of data in the table, and can use constant values in a statement to make guesses about the likely result of executing the statement. Since this data is unavailable when planning prepared statements with parameters, the chosen plan may be suboptimal. To examine the query plan PostgreSQL has chosen for a prepared statement, use EXPLAIN EXECUTE.
For more information on query planning and the statistics collected by PostgreSQL for that purpose, see the ANALYZE [analyze(7)] documentation.
