PostgreSQL : Prepared statement

Let's continue the dive into PostgreSQL! This time something less complicated but that can be useful: Prepared statements.
I had this idea by reading an article by Franck Pachot.

Custom Plan vs. Generic Plan

Prepared statements enable us to optimize performance! These are server-side objects that are destroyed when the session is over. This means that you can't reuse them across sessions. You will always have to 'prepare the statement' again (we will see how after).

Nevertheless, what is interesting about the prepared statement is it can use a generic plan. Indeed you have two kinds of plans, generic plan and custom plan.

The generic plan is a plan that can be used across sessions. And will avoid the cost of planning and parsing your query again.

You can play with your mode of planning with the plan_cache_mode variable. For example, if you want to force a generic_plan, you can do the following :

ALTER DATABASE mart1 SET plan_cache_mode = force_generic_plan

Be careful by doing this; you will force the generic plan. But sometimes there are better ones to use. And upfront costs can be worth it.

Online Art Auction

Let's say that we are an art auction company. We are a brand new company, and we will build our data model.

CREATE TABLE artwork (
    id uuid primary key,
    name text,
    author text
)

CREATE TABLE auction_art (
    id uuid primary key,
    artwork_id uuid,
    actual_price float,
    FOREIGN KEY (artwork_id) REFERENCES artwork(id)
)

Imagine users wanting to know the last offer for an online auction on a masterpiece. The corresponding SQL will be the following :

SELECT price FROM auction_art
WHERE id = id_you_want;

Each time our customer hits refresh, the query will be launched for the same user for the same piece of art. This will lead to PostgreSQL to parse and plan the again the query.

This is an upfront cost, and you will likely want to decrease the load on your database. Then to avoid your database doing the parsing and planning of the query again, you can use PREPARED STATEMENT.

PREPARE auction_price (uuid) AS
SELECT price FROM auction_art
WHERE id = $1;

Each time the customer hits the refresh button, this will use the auction_price to prepare a statement instead of doing the SQL query again.

I told you about the generic plan and custom plan earlier. Let's look at how to spot the difference.
If we look further into the first query we wrote, we can see that if we provide an id, we will have the following query plan.

EXPLAIN SELECT * FROM auction_art WHERE id = 'qsdlfkj24'
;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on auction_art  (cost=0.00..24.12 rows=6 width=44)
   Filter: (id = 'qsdlfkj24')
(2 rows)

You forced the generic plan if you altered your database with the command in the first part. If you look at the query plan, you will see little difference except the $1.

EXPLAIN EXECUTE auction_price('qsdlfkj24');
;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on auction_art  (cost=0.00..24.12 rows=6 width=44)
   Filter: (id = $1)
(2 rows)

This $1 says that you are using a generic plan. And this means that you avoid the cost of planning and parsing the query again.

Nevertheless, as we said, the generic plan is sometimes the best, and here we forced it. Let's put back the default behavior.

ALTER DATABASE mart1 SET plan_cache_mode = auto

Now if you connect again to another session and try to look at the query plan, you will see this :

EXPLAIN EXECUTE auction_price('qsdlfkj24');
;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on auction_art  (cost=0.00..24.12 rows=6 width=44)
   Filter: (id = 'qsdlfkj24')
(2 rows)

No generic plan was used but a custom plan. But if you execute this at least five times the

/// execute at least five time
EXPLAIN EXECUTE auction_price('qsdlfkj24');
;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on auction_art  (cost=0.00..24.12 rows=6 width=44)
   Filter: (id = $1)
(2 rows)

You can check in the source code of Postgres here if you want to find how it figures out what plan to use.

So what?

I presented you with a silly example; it is more complex. Nevertheless, you have to dive deep when it comes to performance issues. A prepared statement can be a valuable asset to have in your toolbox.

I'd like to know if PL/PGSQL is the only way to have a persistent query. Can we use the generic plan with PL/PGSQL?
I might be digging in this in another article :).

Resources

These articles are great resources where I found useful information :

https://www.dbi-services.com/blog/what-are-custom-and-generic-plans-in-postgresql/

https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/

Keep in Touch

On Twitter : @yet_anotherDev

On Linkedin : Lucas Barret