When a prepared statement is created in PostgreSQL, the server has two possible strategies for executing it: use a generic plan or use a custom plan. Each time we run a prepared statement it will chose one of these two plans.

By default, it will use a custom plan for the first five runs. After that it will decide whether or not it’s more efficient to use a generic plan for subsequent executions, or stay with a custom plan.

This begs the question; how do we check to see which plan PostgreSQL chose?

Fortunately, PostgreSQL provides us with an easy way to do that; the pg_prepared_statements view. The pg_prepared_statements view returns a list of all prepared statements created in the current session. Included in this list are two columns that indicate how many times each query plan was used (generic vs custom).

Example

Let’s create a simple table, some prepared statements, and then get a list of those prepared statements, along with the number of times each query plan was used.

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;

Execute the Prepared Statements

Let’s run a couple of the prepared statements several times:

EXECUTE get_user_by_id(101);
EXECUTE get_user_by_id(101);
EXECUTE get_user_by_id(101);
EXECUTE get_user_by_id(101);
EXECUTE get_user_by_id(101);
EXECUTE get_user_by_id(101);
EXECUTE get_user_by_id(101);
EXECUTE get_user_by_username('homer');
EXECUTE get_user_by_username('homer');
EXECUTE get_user_by_username('homer');

That will be enough to test out the “five run” rule.

List All Prepared Statements

We can run the following code to see them:

SELECT name, generic_plans, custom_plans 
FROM pg_prepared_statements;

Output:

         name         | generic_plans | custom_plans 
----------------------+---------------+--------------
 get_user_by_id       |             2 |            5
 get_user_by_email    |             0 |            0
 get_user_by_username |             0 |            3

We executed the first prepared statement seven times and so we can see that it chose the custom plan for the first five runs, and then resorted to the generic plan.

As for the get_user_by_username statement, we only ran that three times, which is still within the first five runs (recall that PostgreSQL uses a custom plan for the first five runs before choosing whether or not to switch to a generic plan).

As for the get_user_by_email prepared statement, I didn’t execute that at all and so it’s still showing zero in both columns.