Prepared statement gains

[worekleszczy]

Hello,

going into database logs I was surprised to see that all queries are run as unnamed prepared statements.

E.g for a postgres database with a table:

CREATE TABLE users (
id SERIAL PRIMARY KEY,
name text NOT NULL,
surname text NOT NULL
);

and a statement:

def insert(name: String, surname: String): ConnectionIO[Int] = fr"INSERT INTO users(name, surname) VALUES ($name, $surname)".update.run

insert("NAME", "SURNAME").quick.unsafeRunSync

I can see in logs:

2023-01-18 00:02:40.006 CET [94971] LOG:  execute <unnamed>: BEGIN
2023-01-18 00:02:40.007 CET [94971] LOG:  execute <unnamed>: INSERT INTO users(name, surname) VALUES ($1, $2) 
2023-01-18 00:02:40.007 CET [94971] DETAIL:  parameters: $1 = 'NAME', $2 = 'SURNAME'
2023-01-18 00:02:40.010 CET [94971] LOG:  execute S_1: COMMIT

I tried to educate myself on unnamed prepared statements, but didn't find any useful information apart from that it's only support on the wire protocol. Can you tell me what gains does it provide? Can a database still remove planning a query part event though at a first glance it's not possible to distinguish one unnamed prepared statement from the other?

[jatcwang]

If you're using Postgres JDBC (pgjdbc) driver, it already transparently created server-side prepared statement to cache commonly executed statements (This is scoped per DB connection so if you're using a connection pool it should just work).

Keen to see some benchmarks though if anyone wants to compare plain doobie vs reusing the same PreparedStatement object. (The linked docs do say that reusing the PreparedStatment object is more efficient, but I'm not sure by how much)