PREPARE

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 parsing and analysis work, while still allowing the execution plan to depend on the specific parameter values supplied.

Prepared statements can accept parameters: values that will be substituted into the statement at execution time. When creating a prepared statement, parameters can be referenced positionally using $1, $2, etc. A list of parameter data types can optionally be specified. When a parameter's data type is not specified or is declared as unknown, its type is inferred from the context in which the parameter is first referenced (if possible). When executing the statement, actual values for these parameters are specified in the EXECUTE statement. For more information on this, see EXECUTE.

Prepared statements only exist for the duration of the current database session. When the session ends, the prepared statement disappears and must be re-created before it can be used again. This also means that a prepared statement cannot be used simultaneously by multiple database clients. However, each client can create its own prepared statements to use. Prepared statements can be manually removed with the DEALLOCATE command.

Prepared statements can provide the greatest performance advantage when a session is going to execute a large number of similar statements. The performance difference will be particularly noticeable if the statement is complex (difficult to plan or rewrite), for example, if the query involves joins of many tables or requires the application of multiple rules. If the statement is relatively easy to plan and rewrite but expensive to execute, the performance advantage of prepared statements will be less significant.

Parameters

name

An arbitrary name for this particular prepared statement. It must be unique within a session and will subsequently be used to execute or deallocate a previously prepared statement.

data_type

The data type of a parameter to the prepared statement. If a specific parameter's data type is not specified or is specified as unknown, it will be inferred from the context in which the parameter is first referenced. To reference parameters within the prepared statement itself, you can use $1, $2, etc.

statement

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

Notes

Prepared statements can be executed using either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in the call. Using a generic plan avoids planning overhead, but in some cases, a custom plan can be much more efficient to execute because the planner can leverage knowledge of the parameter values. (Of course, if the prepared statement has no parameters, this is irrelevant, and a generic plan should always be used.)

By default (i.e., when plan_cache_mode is set to auto), the server will automatically choose between using a generic or custom plan for prepared statements that have parameters. The current rule is that the first five executions are done using custom plans, and the average estimated cost of these plans is computed. Then a generic plan is created and its estimated cost is compared to the average cost of the custom plans. If the generic plan's cost is not significantly higher than the average custom plan cost, subsequent executions will use the generic plan, as this makes repeated re-planning less desirable.

This heuristic approach can be overridden to force the server to use either generic or custom plans by setting plan_cache_mode to force_generic_plan or force_custom_plan, respectively. This setting is primarily useful if the generic plan's cost estimate is suboptimal for some reason, even though its actual cost is much higher than the actual cost of a custom plan.

To examine the execution plan Halo uses for a prepared statement, you can use EXPLAIN. For example:

EXPLAIN EXECUTE name(parameter_values);

If a generic plan is being used, it will contain parameter symbols $n, while a custom plan will have the supplied parameter values substituted in.

Although prepared statements are primarily intended to avoid repetitive parsing, analysis, and planning of statements, Halo will force re-analysis and re-planning of the statement whenever the definition (DDL) of any database object used in the statement has changed since the last time the prepared statement was used. Also, if the value of search_path changes, the statement will be re-parsed using the new search_path. These rules make the use of prepared statements semantically almost equivalent to repeatedly submitting the same query text, but with a performance benefit (if no object definitions have changed, and especially if the optimal plan remains the same). One example of imperfect semantic equivalence is: if the statement references a table with an unqualified name, and a new table with the same name is later created in a schema that appears earlier in the search_path, automatic re-parsing will not occur because the object used by the statement has not changed.

However, if some other change causes re-parsing, subsequent uses will reference the new table.

All available prepared statements in a session can be viewed by querying the pg_prepared_statements system view.

Examples

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

PREPARE fooplan (int, text, bool, numeric) AS

INSERT INTO foo VALUES($1, $2, $3, $4);

EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

-- Create a prepared statement for a SELECT statement, then execute it:

PREPARE usrrptplan (int) AS

SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid

AND l.date = $2;

EXECUTE usrrptplan(1, current_date);

-- In this example, the data type of the second parameter is not specified, so it will be inferred from the context where $2 is used.

See Also

DEALLOCATE, EXECUTE