1PREPARE(7) PostgreSQL 12.6 Documentation PREPARE(7)
2
6 PREPARE - prepare a statement for execution
7
9 PREPARE name [ ( data_type [, ...] ) ] AS statement
10
12 PREPARE creates a prepared statement. A prepared statement is a
13 server-side object that can be used to optimize performance. When the
14 PREPARE statement is executed, the specified statement is parsed,
15 analyzed, and rewritten. When an EXECUTE command is subsequently
16 issued, the prepared statement is planned and executed. This division
17 of labor avoids repetitive parse analysis work, while allowing the
18 execution plan to depend on the specific parameter values supplied.
19 Prepared statements can take parameters: values that are substituted
21 into the statement when it is executed. When creating the prepared
22 statement, refer to parameters by position, using $1, $2, etc. A
23 corresponding list of parameter data types can optionally be specified.
24 When a parameter's data type is not specified or is declared as
25 unknown, the type is inferred from the context in which the parameter
26 is first referenced (if possible). When executing the statement,
27 specify the actual values for these parameters in the EXECUTE
28 statement. Refer to EXECUTE(7) for more information about that.
29 Prepared statements only last for the duration of the current database
31 session. When the session ends, the prepared statement is forgotten, so
32 it must be recreated before being used again. This also means that a
33 single prepared statement cannot be used by multiple simultaneous
34 database clients; however, each client can create their own prepared
35 statement to use. Prepared statements can be manually cleaned up using
36 the DEALLOCATE(7) command.
37 Prepared statements potentially have the largest performance advantage
39 when a single session is being used to execute a large number of
40 similar statements. The performance difference will be particularly
41 significant if the statements are complex to plan or rewrite, e.g., if
42 the query involves a join of many tables or requires the application of
43 several rules. If the statement is relatively simple to plan and
44 rewrite but relatively expensive to execute, the performance advantage
45 of prepared statements will be less noticeable.
46
48 name
49 An arbitrary name given to this particular prepared statement. It
50 must be unique within a single session and is subsequently used to
51 execute or deallocate a previously prepared statement.
52 data_type
54 The data type of a parameter to the prepared statement. If the data
55 type of a particular parameter is unspecified or is specified as
56 unknown, it will be inferred from the context in which the
57 parameter is first referenced. To refer to the parameters in the
58 prepared statement itself, use $1, $2, etc.
59 statement
61 Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.
62
64 A prepared statement can be executed with either a generic plan or a
65 custom plan. A generic plan is the same across all executions, while a
66 custom plan is generated for a specific execution using the parameter
67 values given in that call. Use of a generic plan avoids planning
68 overhead, but in some situations a custom plan will be much more
69 efficient to execute because the planner can make use of knowledge of
70 the parameter values. (Of course, if the prepared statement has no
71 parameters, then this is moot and a generic plan is always used.)
72 By default (that is, when plan_cache_mode is set to auto), the server
74 will automatically choose whether to use a generic or custom plan for a
75 prepared statement that has parameters. The current rule for this is
76 that the first five executions are done with custom plans and the
77 average estimated cost of those plans is calculated. Then a generic
78 plan is created and its estimated cost is compared to the average
79 custom-plan cost. Subsequent executions use the generic plan if its
80 cost is not so much higher than the average custom-plan cost as to make
81 repeated replanning seem preferable.
82 This heuristic can be overridden, forcing the server to use either
84 generic or custom plans, by setting plan_cache_mode to
85 force_generic_plan or force_custom_plan respectively. This setting is
86 primarily useful if the generic plan's cost estimate is badly off for
87 some reason, allowing it to be chosen even though its actual cost is
88 much more than that of a custom plan.
89 To examine the query plan PostgreSQL is using for a prepared statement,
91 use EXPLAIN(7), for example
92 EXPLAIN EXECUTE name(parameter_values);
94 If a generic plan is in use, it will contain parameter symbols $n,
96 while a custom plan will have the supplied parameter values substituted
97 into it.
98 For more information on query planning and the statistics collected by
100 PostgreSQL for that purpose, see the ANALYZE(7) documentation.
101 Although the main point of a prepared statement is to avoid repeated
103 parse analysis and planning of the statement, PostgreSQL will force
104 re-analysis and re-planning of the statement before using it whenever
105 database objects used in the statement have undergone definitional
106 (DDL) changes since the previous use of the prepared statement. Also,
107 if the value of search_path changes from one use to the next, the
108 statement will be re-parsed using the new search_path. (This latter
109 behavior is new as of PostgreSQL 9.3.) These rules make use of a
110 prepared statement semantically almost equivalent to re-submitting the
111 same query text over and over, but with a performance benefit if no
112 object definitions are changed, especially if the best plan remains the
113 same across uses. An example of a case where the semantic equivalence
114 is not perfect is that if the statement refers to a table by an
115 unqualified name, and then a new table of the same name is created in a
116 schema appearing earlier in the search_path, no automatic re-parse will
117 occur since no object used in the statement changed. However, if some
118 other change forces a re-parse, the new table will be referenced in
119 subsequent uses.
120 You can see all prepared statements available in the session by
122 querying the pg_prepared_statements system view.
123
125 Create a prepared statement for an INSERT statement, and then execute
126 it:
127 PREPARE fooplan (int, text, bool, numeric) AS
129 INSERT INTO foo VALUES($1, $2, $3, $4);
130 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
131 Create a prepared statement for a SELECT statement, and then execute
133 it:
134 PREPARE usrrptplan (int) AS
136 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
137 AND l.date = $2;
138 EXECUTE usrrptplan(1, current_date);
139 In this example, the data type of the second parameter is not
141 specified, so it is inferred from the context in which $2 is used.
142
144 The SQL standard includes a PREPARE statement, but it is only for use
145 in embedded SQL. This version of the PREPARE statement also uses a
146 somewhat different syntax.
147
149 DEALLOCATE(7), EXECUTE(7)
150PostgreSQL 12.6 2021 PREPARE(7)