Postgresql PREPARE usage

Name

PREPARE -- create a prepared Query

Synopsis

PREPARE plan_name [ (datatype [, ...] ) ] AS statement

Description

PREPARECreate a prepared query. A Prepared query is a server-side object that can be used to optimize performance. In executionPREPAREStatement, the specified query is analyzed, rewritten, and planned. WhenEXECUTEThe prepared query only needs to be executed. Therefore, the analysis, rewriting, and planning phases are executed only once, rather than once each query execution.

The prepared query can accept the parameter: it replaces the value in the query when executed. To give a prepared query declaration parameter, we needPREPAREThe statement contains a list of data types. In the query itself, you can reference these parameters by location, such$1,$2. When performing a queryEXECUTEThe statement declares the actual values for these parameters.
ReferenceEXECUTEObtain more information.

The prepared query is stored locally (in the current backend) and only exists during the current database session. If the client exits, the prepared query will be forgotten, so we must create a new one before it is reused. This also means that a prepared query cannot be used by multiple database clients at the same time; however, each client can create its own prepared query for use. Prepared statements can be usedDEALLOCATECommand to manually clear.

If a session is used to execute a large number of similar queries, the prepared query can have the maximum performance advantage. If the query is complex and requires complex planning or rewriting, the performance gap will be very obvious. For example, if you want to design connections to many tables, or you have multiple rules that require applications. If the query planning and rewriting are relatively simple, and the execution overhead is quite large, the performance advantages of prepared queries are not that obvious.

 

Parameters

Plan_name

Give this specific name that is ready for query. It must be unique in a session and used to execute or delete a prepared query.

Datatype

The data type of a parameter to be queried. To reference this parameter within the prepared query, use$1,$2.

Statement

AnySELECT,INSERT,UPDATE, OrDELETEQuery.

Note:

In some cases, it may be better for PostgreSQL to generate a query plan for a prepared query than to generate a query that is submitted and executed according to the common method. This is because when the query is planned (when the optimizer view determines the optimal query planning), the actual values of any parameters declared in the query are invisible. PostgreSQL collects statistics on data distribution in the table,
You can also use constants in the query to guess the possible results of the query. Because the data is not available for planning which queries with parameters are prepared, it may be a good plan. To check the query plan selected by PostgreSQL for the prepared query, useEXPLAIN.

For more information about query planning and statistics collected by PostgreSQL for query optimization purposes, seeANALYZEDocumentation.

Example

ForINSERTStatement to create a prepared query and 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);

ForSELECT
Statement to create an executed query and then execute it:

PREPARE usrrptplan (int, date) 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);