Performance of prepared statements vs statements with literals

[alespour]

According to the documentation, pgx automatically caches prepared statements. I'm using pgxpol from which I hijack connections for some tasks. I've ran benchmark to compare performance of SELECT statements with literals a prepared statement with 10 args, all in WHERE clause conditions (WHERE x = $1 etc). Prepared statements are ~15-20% slower. I'm no expert on databases and SQL, so I naively expected better performance of prepared statements.

Was I wrong to expect better performance?

I've also checked how to manually prepare statements in AfterConnect beforehand in pool, but could not make it work, per discussion in #791. In the repo, in conn_test.go, there is this code (simplified)

	conn.Prepare(context.Background(), "test", "select $1::varchar")
	conn.QueryRow(context.Background(), "test", "hello").Scan(&s)

So I tried

	conn.Prepare(context.Background(), "mytstmt", "select * from x where hour=$1 AND day=$2")
	rows, err := conn.Query(context.Background(), "mytstmt", 14, 5) // runtime error - expected 0 args got 2

But it is apparently wrong, because "mytstmt" is interpreted as SQL text and not prepared statement name.

Thank you in advance.

[alespour]

To answer my question, I have no explanation as to why prepared statement is slower in my testing environment. But I've verified that with automatic prepared statement caching, the code execution goes via

pgx/conn.go

Line 816 in 672c4a3

rows.resultReader = c.pgConn.ExecPrepared(ctx, sd.Name, c.eqb.ParamValues, c.eqb.ParamFormats, resultFormats)

As for using pgxpool.AfterConnect to manually prepare them, prepared statements may get lost during hijack (my guess, I have no idea), but since automatic caching works ok, I do not need to solve this "problem".

Thanks.

[jackc]

There may be a little overhead on the first execution due to preparing the statement, but in general it should be significantly faster on subsequent executions. Try benchmarking the query hundreds or thousands of times and see if you see the difference.

But as you found, pgx automatically prepares and caches statements. It is very rare to need to manually prepare statements.