PREPARE

Name

PREPARE -- prepare a statement for
execution

Synopsis

PREPARE name [ ( data_type [, ...] ) ] 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,
analyzed, and rewritten. When an EXECUTE
command is subsequently issued, the prepared statement is planned
and executed. This division of labor avoids repetitive parse
analysis work, while allowing the execution plan to depend on the
specific parameter values supplied.

Prepared statements can take parameters: values that are
substituted into the statement when it is executed. When creating
the prepared statement, refer to parameters by position, using
$1, $2, etc. A
corresponding list of parameter data types can optionally be
specified. When a parameter's data type is not specified or is
declared as unknown, the type is
inferred from the context in which the parameter is used (if
possible). When executing the statement, specify the actual
values for these parameters in the EXECUTE statement. Refer to EXECUTE for more information about
that.

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.
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 can be manually cleaned up using the DEALLOCATE command.

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

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.

data_type

The data type of a parameter to the prepared statement.
If the data type of a particular parameter is unspecified
or is specified as unknown, it
will be inferred from the context in which the parameter is
used. To refer to the parameters in the prepared statement
itself, use $1, $2, etc.

statement

Any SELECT, INSERT, UPDATE,
DELETE, or VALUES statement.

Notes

If a prepared statement is executed enough times, the server
may eventually decide to save and re-use a generic plan rather
than re-planning each time. This will occur immediately if the
prepared statement has no parameters; otherwise it occurs only if
the generic plan appears to be not much more expensive than a
plan that depends on specific parameter values. Typically, a
generic plan will be selected only if the query's performance is
estimated to be fairly insensitive to the specific parameter
values supplied.

To examine the query plan PostgreSQL is using for a prepared
statement, use EXPLAIN. If a
generic plan is in use, it will contain parameter symbols
$n,
while a custom plan will have the current actual parameter values
substituted into it.

For more information on query planning and the statistics
collected by PostgreSQL for that
purpose, see the ANALYZE
documentation.

Although the main point of a prepared statement is to avoid
repeated parse analysis and planning of the statement,
PostgreSQL will force
re-analysis and re-planning of the statement before using it
whenever database objects used in the statement have undergone
definitional (DDL) changes since the previous use of the prepared
statement. Also, if the value of search_path
changes from one use to the next, the statement will be re-parsed
using the new search_path. (This latter
behavior is new as of PostgreSQL
9.3.) These rules make use of a prepared statement semantically
almost equivalent to re-submitting the same query text over and
over, but with a performance benefit if no object definitions are
changed, especially if the best plan remains the same across
uses. An example of a case where the semantic equivalence is not
perfect is that if the statement refers to a table by an
unqualified name, and then a new table of the same name is
created in a schema appearing earlier in the search_path, no automatic re-parse will occur
since no object used in the statement changed. However, if some
other change forces a re-parse, the new table will be referenced
in subsequent uses.

You can see all prepared statements available in the session
by querying the pg_prepared_statements system view.

Examples

Create a prepared statement for an INSERT statement, and then execute it: