PREPARE(l) SQL - Language Statements (2002-11-22) PREPARE(l)
NAME
PREPARE - create a prepared query
SYNOPSIS
PREPARE plan_name [ (datatype [, ...] ) ] AS query
INPUTS
plan_name
An arbitrary name given to this particular prepared
query. It must be unique within a single session, and
is used to execute or remove a previously prepared
query.
datatype
The data-type of a parameter to the prepared query. To
refer to the parameters in the prepared query itself,
use $1, $2, etc.
OUTPUTS
PREPARE
The query has been prepared successfully.
DESCRIPTION
PREPARE creates a prepared query. A prepared query is a
server-side object that can be used to optimize performance.
When the PREPARE statement is executed, the specified query
is parsed, rewritten, and planned. When a subsequent EXECUTE
statement is issued, the prepared query need only be
executed. Thus, the parsing, rewriting, and planning stages
are only performed once, instead of every time the query is
executed.
Prepared queries can take parameters: values that are
substituted into the query when it is executed. To specify
the parameters to a prepared query, include a list of data-
types with the PREPARE statement. In the query itself, you
can refer to the parameters by position using $1, $2, etc.
When executing the query, specify the actual values for
these parameters in the EXECUTE statement -- refer to
EXECUTE [execute(l)] for more information.
Prepared queries are stored locally (in the current
backend), and only exist for the duration of the current
database session. When the client exits, the prepared query
is forgotten, and so it must be re-created before being used
again. This also means that a single prepared query cannot
be used by multiple simultaneous database clients; however,
each client can create their own prepared query to use.
Prepared queries have the largest performance advantage when
Page 1 (printed 3/24/03)
PREPARE(l) SQL - Language Statements (2002-11-22) PREPARE(l)
a single backend is being used to execute a large number of
similar queries. The performance difference will be
particularly significant if the queries 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 query is relatively simple to plan and rewrite but
relatively expensive to execute, the performance advantage
of prepared queries will be less noticeable.
NOTES
In some situations, the query plan produced by PostgreSQL
for a prepared query may be inferior to the plan produced if
the query were submitted and executed normally. This is
because when the query is planned (and the optimizer
attempts to determine the optimal query plan), the actual
values of any parameters specified in the query are
unavailable. PostgreSQL collects statistics on the
distribution of data in the table, and can use constant
values in a query to make guesses about the likely result of
executing the query. Since this data is unavailable when
planning prepared queries with parameters, the chosen plan
may be sub-optimal.
For more information on query planning and the statistics
collected by PostgreSQL for query optimization purposes, see
the ANALYZE [analyze(l)] documentation.
COMPATIBILITY
SQL92
SQL92 includes a PREPARE statement, but it is only for use
in embedded SQL clients. The PREPARE statement implemented
by PostgreSQL also uses a somewhat different syntax.
Page 2 (printed 3/24/03)
