1PREPARE(7) PostgreSQL 11.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 Prepared statements can use generic plans rather than re-planning with
65 each set of supplied EXECUTE values. This occurs immediately for
66 prepared statements with no parameters; otherwise it occurs only after
67 five or more executions produce plans whose estimated cost average
68 (including planning overhead) is more expensive than the generic plan
69 cost estimate. Once a generic plan is chosen, it is used for the
70 remaining lifetime of the prepared statement. Using EXECUTE values
71 which are rare in columns with many duplicates can generate custom
72 plans that are so much cheaper than the generic plan, even after adding
73 planning overhead, that the generic plan might never be used.
74 A generic plan assumes that each value supplied to EXECUTE is one of
76 the column's distinct values and that column values are uniformly
77 distributed. For example, if statistics record three distinct column
78 values, a generic plan assumes a column equality comparison will match
79 33% of processed rows. Column statistics also allow generic plans to
80 accurately compute the selectivity of unique columns. Comparisons on
81 non-uniformly-distributed columns and specification of non-existent
82 values affects the average plan cost, and hence if and when a generic
83 plan is chosen.
84 To examine the query plan PostgreSQL is using for a prepared statement,
86 use EXPLAIN(7), e.g. EXPLAIN EXECUTE. If a generic plan is in use, it
87 will contain parameter symbols $n, while a custom plan will have the
88 supplied parameter values substituted into it. The row estimates in the
89 generic plan reflect the selectivity computed for the parameters.
90 For more information on query planning and the statistics collected by
92 PostgreSQL for that purpose, see the ANALYZE(7) documentation.
93 Although the main point of a prepared statement is to avoid repeated
95 parse analysis and planning of the statement, PostgreSQL will force
96 re-analysis and re-planning of the statement before using it whenever
97 database objects used in the statement have undergone definitional
98 (DDL) changes since the previous use of the prepared statement. Also,
99 if the value of search_path changes from one use to the next, the
100 statement will be re-parsed using the new search_path. (This latter
101 behavior is new as of PostgreSQL 9.3.) These rules make use of a
102 prepared statement semantically almost equivalent to re-submitting the
103 same query text over and over, but with a performance benefit if no
104 object definitions are changed, especially if the best plan remains the
105 same across uses. An example of a case where the semantic equivalence
106 is not perfect is that if the statement refers to a table by an
107 unqualified name, and then a new table of the same name is created in a
108 schema appearing earlier in the search_path, no automatic re-parse will
109 occur since no object used in the statement changed. However, if some
110 other change forces a re-parse, the new table will be referenced in
111 subsequent uses.
112 You can see all prepared statements available in the session by
114 querying the pg_prepared_statements system view.
115
117 Create a prepared statement for an INSERT statement, and then execute
118 it:
119 PREPARE fooplan (int, text, bool, numeric) AS
121 INSERT INTO foo VALUES($1, $2, $3, $4);
122 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
123 Create a prepared statement for a SELECT statement, and then execute
125 it:
126 PREPARE usrrptplan (int) AS
128 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
129 AND l.date = $2;
130 EXECUTE usrrptplan(1, current_date);
131 Note that the data type of the second parameter is not specified, so it
133 is inferred from the context in which $2 is used.
134
136 The SQL standard includes a PREPARE statement, but it is only for use
137 in embedded SQL. This version of the PREPARE statement also uses a
138 somewhat different syntax.
139
141 DEALLOCATE(7), EXECUTE(7)
142PostgreSQL 11.6 2019 PREPARE(7)