Prepared statements are a precompiled SQL queries that we can execute multiple times with different arguments. They are limited to the current session, so we can only create them and run them from within the same session.

Sometimes we might want to review how many prepared statements we’ve created. Or we might need to deallocate one, but we’ve forgotten its name.

No problem. We can use the pg_prepared_statements view to see all of our prepared statements.

Example

Let’s create a simple table, some prepared statements, and then get a list of those prepared statements.

Create a Table

Suppose we create a table like this:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Create Prepared Statements

And then we create a few prepared statements, like this:

PREPARE get_user_by_id (int) AS
SELECT user_id, username, email FROM users WHERE user_id = $1;

PREPARE get_user_by_username (varchar(50)) AS
SELECT user_id, username, email FROM users WHERE username = $1;

PREPARE get_user_by_email (varchar(100)) AS
SELECT user_id, username, email FROM users WHERE email = $1;

List All Prepared Statements

We can run the following code to see them:

SELECT * FROM pg_prepared_statements;

Output:

         name         |                            statement                            |         prepare_time          |    parameter_types    |                   result_types                    | from_sql | generic_plans | custom_plans 
----------------------+-----------------------------------------------------------------+-------------------------------+-----------------------+---------------------------------------------------+----------+---------------+--------------
 get_user_by_id       | PREPARE get_user_by_id (int) AS                                +| 2024-09-12 09:05:03.578941+07 | {integer}             | {integer,"character varying","character varying"} | t        |             0 |            0
                      | SELECT user_id, username, email FROM users WHERE user_id = $1;  |                               |                       |                                                   |          |               | 
 get_user_by_email    | PREPARE get_user_by_email (varchar(100)) AS                    +| 2024-09-12 09:05:03.89788+07  | {"character varying"} | {integer,"character varying","character varying"} | t        |             0 |            0
                      | SELECT user_id, username, email FROM users WHERE email = $1;    |                               |                       |                                                   |          |               | 
 get_user_by_username | PREPARE get_user_by_username (varchar(50)) AS                  +| 2024-09-12 09:05:03.581152+07 | {"character varying"} | {integer,"character varying","character varying"} | t        |             0 |            0
                      | SELECT user_id, username, email FROM users WHERE username = $1; |                               |                       |                                                   |          |               | 
(3 rows)

If the session had any other prepared statements they would be listed here too.

We can use this view to see how many times a generic plan was chosen vs a custom plan (look in the generic_plans and custom_plans columns).