This section is all about prepared queries. What is a prepared query? Consider the following example:

SELECT * FROM website WHERE domain = 'www.cybertec.at';

PostgreSQL will parse the query to check for syntax errors and so on. Then it will go through the rewrite system and hit the PostgreSQL optimizer, which then comes up with a good plan. Finally, the executor will perform all the steps selected by the query planner. If this type of query is executed over and over again for different domains, the parsing and planning process will also have to run over and over again, which is very time consuming.

Prepared queries try to solve exactly that problem. The idea is that the backend process will cache the plan and reuse it just in case it is needed again. The beauty is that sending the parameters instead of the complete query can be enough. Usually, prepared statements are happening behind the scenes when desired (this is usually done through the driver if the driver is prepared for it). However, you can also prepare queries manually:

test=# h PREPARE
Command:     PREPARE
Description: prepare a statement for execution
Syntax:
PREPARE name [ ( data_type [, ...] ) ] AS statement

As mentioned already, something similar is done by the driver. However, it also works manually:

test=# PREPARE myplan(int) AS SELECT $1;
PREPARE
test=# EXECUTE myplan(10);
 ?column? 
----------
       10
(1 row)

In this example, a plan called myplan that accepts one parameter is created. Parameters passed to a prepared query are numbers such as $1, $2, $3, and so on. To run a plan, EXECUTE is used here.

Of course, there is some protocol-level support for optimizing this process, and it is highly recommended to make use of this functionality.

However, in some cases, there can be problems. If a client application creates thousands or even millions of prepared plans, the memory consumption of the backend may grow. To figure out whether your active database connection has created too many prepared statements, a system view can be consulted:

test=# d pg_prepared_statements
        View "pg_catalog.pg_prepared_statements"
     Column      |           Type           | Modifiers 
-----------------+--------------------------+-----------
 name            | text                     | 
 statement       | text                     | 
 prepare_time    | timestamp with time zone | 
 parameter_types | regtype[]                | 
 from_sql        | boolean                  | 

The pg_prepared_statements view tells us which statements have been prepared in our database connection.

If you want to remove a statement from a backend, you can use the DEALLOCATE command:

test=# h DEALLOCATE 
Command:     DEALLOCATE
Description: deallocate a prepared statement
Syntax:
DEALLOCATE [ PREPARE ] { name | ALL }

Another important point is that prepared queries are not only about speed but also about SQL injections. A prepared query is a good way to avoid security loopholes altogether.