diff options
Diffstat (limited to 'doc/src/sgml/ref/prepare.sgml')
| -rw-r--r-- | doc/src/sgml/ref/prepare.sgml | 258 |
1 files changed, 258 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml new file mode 100644 index 0000000..d7b8539 --- /dev/null +++ b/doc/src/sgml/ref/prepare.sgml @@ -0,0 +1,258 @@ +<!-- +doc/src/sgml/ref/prepare.sgml +PostgreSQL documentation +--> + +<refentry id="sql-prepare"> + <indexterm zone="sql-prepare"> + <primary>PREPARE</primary> + </indexterm> + + <indexterm zone="sql-prepare"> + <primary>prepared statements</primary> + <secondary>creating</secondary> + </indexterm> + + <refmeta> + <refentrytitle>PREPARE</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>PREPARE</refname> + <refpurpose>prepare a statement for execution</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +PREPARE <replaceable class="parameter">name</replaceable> [ ( <replaceable class="parameter">data_type</replaceable> [, ...] ) ] AS <replaceable class="parameter">statement</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>PREPARE</command> creates a prepared statement. A prepared + statement is a server-side object that can be used to optimize + performance. When the <command>PREPARE</command> statement is + executed, the specified statement is parsed, analyzed, and rewritten. + When an <command>EXECUTE</command> 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. + </para> + + <para> + 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 + <literal>$1</literal>, <literal>$2</literal>, 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 + <literal>unknown</literal>, the type is inferred from the context + in which the parameter is first referenced (if possible). When executing the + statement, specify the actual values for these parameters in the + <command>EXECUTE</command> statement. Refer to <xref + linkend="sql-execute"/> for more + information about that. + </para> + + <para> + 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 <link linkend="sql-deallocate"><command>DEALLOCATE</command></link> command. + </para> + + <para> + Prepared statements potentially 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, e.g., + 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. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">data_type</replaceable></term> + <listitem> + <para> + The data type of a parameter to the prepared statement. If the + data type of a particular parameter is unspecified or is + specified as <literal>unknown</literal>, it will be inferred + from the context in which the parameter is first referenced. To refer to the + parameters in the prepared statement itself, use + <literal>$1</literal>, <literal>$2</literal>, etc. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">statement</replaceable></term> + <listitem> + <para> + Any <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, + <command>DELETE</command>, <command>MERGE</command>, or <command>VALUES</command> + statement. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1 id="sql-prepare-notes"> + <title>Notes</title> + + <para> + A prepared statement can be executed with either a <firstterm>generic + plan</firstterm> or a <firstterm>custom plan</firstterm>. 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 that call. + Use of a generic plan avoids planning overhead, but in some situations + a custom plan will be much more efficient to execute because the planner + can make use of knowledge of the parameter values. (Of course, if the + prepared statement has no parameters, then this is moot and a generic + plan is always used.) + </para> + + <para> + By default (that is, when <xref linkend="guc-plan-cache_mode"/> is set + to <literal>auto</literal>), the server will automatically choose + whether to use a generic or custom plan for a prepared statement that + has parameters. The current rule for this is that the first five + executions are done with custom plans and the average estimated cost of + those plans is calculated. Then a generic plan is created and its + estimated cost is compared to the average custom-plan cost. Subsequent + executions use the generic plan if its cost is not so much higher than + the average custom-plan cost as to make repeated replanning seem + preferable. + </para> + + <para> + This heuristic can be overridden, forcing the server to use either + generic or custom plans, by setting <varname>plan_cache_mode</varname> + to <literal>force_generic_plan</literal> + or <literal>force_custom_plan</literal> respectively. + This setting is primarily useful if the generic plan's cost estimate + is badly off for some reason, allowing it to be chosen even though + its actual cost is much more than that of a custom plan. + </para> + + <para> + To examine the query plan <productname>PostgreSQL</productname> is using + for a prepared statement, use <link linkend="sql-explain"><command>EXPLAIN</command></link>, for example +<programlisting> +EXPLAIN EXECUTE <replaceable>name</replaceable>(<replaceable>parameter_values</replaceable>); +</programlisting> + If a generic plan is in use, it will contain parameter symbols + <literal>$<replaceable>n</replaceable></literal>, while a custom plan + will have the supplied parameter values substituted into it. + </para> + + <para> + For more information on query planning and the statistics collected + by <productname>PostgreSQL</productname> for that purpose, see + the <xref linkend="sql-analyze"/> + documentation. + </para> + + <para> + Although the main point of a prepared statement is to avoid repeated parse + analysis and planning of the statement, <productname>PostgreSQL</productname> 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 or their planner statistics have + been updated since the previous use of the prepared + statement. Also, if the value of <xref linkend="guc-search-path"/> changes + from one use to the next, the statement will be re-parsed using the new + <varname>search_path</varname>. (This latter behavior is new as of + <productname>PostgreSQL</productname> 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 <varname>search_path</varname>, 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. + </para> + + <para> + You can see all prepared statements available in the session by querying the + <link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link> + system view. + </para> + </refsect1> + + <refsect1 id="sql-prepare-examples" xreflabel="Examples"> + <title>Examples</title> + <para> + Create a prepared statement for an <command>INSERT</command> + statement, and then execute it: +<programlisting> +PREPARE fooplan (int, text, bool, numeric) AS + INSERT INTO foo VALUES($1, $2, $3, $4); +EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00); +</programlisting> + </para> + + <para> + Create a prepared statement for a <command>SELECT</command> + statement, and then execute it: +<programlisting> +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); +</programlisting> + + In this example, the data type of the second parameter is not specified, + so it is inferred from the context in which <literal>$2</literal> is used. + </para> + </refsect1> + <refsect1> + <title>Compatibility</title> + + <para> + The SQL standard includes a <command>PREPARE</command> statement, + but it is only for use in embedded SQL. This version of the + <command>PREPARE</command> statement also uses a somewhat different + syntax. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-deallocate"/></member> + <member><xref linkend="sql-execute"/></member> + </simplelist> + </refsect1> +</refentry> |