Global prepared statements or ?

[davecramer]

So I started to think about the prepared statement issue and pools.

The JDBC driver creates named prepared statements when it deems that the statement may be re-used enough times.
The problem with this is that it currently doesn't work with pools as statements are per connection. When the connection is returned to the pool we have no guarantee that we get the same connection back when we borrow the connection again.
I've been thinking about how global prepared statements might help here. I'm not clear on how many clients there are. Since the application might have thousands of end users (clients) we don't want to create a prepared statement per user. I'm wondering if creating a unique signature for the statement using MD5 or the like to get a signature for the SQL string and creating the same name for each connection might work?

something like

String statementName = UniqueSignature("select .... from foo where ...")
prepare statement name as "select .... from foo where ...");

It would be really nice if we knew in advance what statement names were already created, but either way the worst that would happen here is that we get an error and we can keep going because we know the statement has already been created.

Thoughts ?

[jtv]

Functions..?

[davecramer]

Can you clarify your question ?

[jtv]

Wouldn't you be able to do that by writing a function instead?

[davecramer]

Well, I guess the hard part is to guarantee that the name is unique across all of the connections. I'd be curious if we could create a function to do this?

[davecramer]

There seems to be consensus from the server guys that they want to do something about this. The first step would be to determine what we want.
I'd also like to understand the issue from all of the drivers.

[jtv]

Has the performance problem been solved then? IIRC there was the problem that a prepared statement gets a generic plan, optimised without any knowledge of the specific parameter values you will pass. And so a prepared statement often runs much slower than a regular statement containing the same SQL (but with the parameter values known at the same time).
If that has been resolved on the server side, then great. Until then, it may not be so useful to auto-prepare statements.

[davecramer]

The generic plan isn't always the slowest it is sometimes the slowest. The point of using the generic plan is that it removes the planning stage. However you can force the custom plan see

By default (that is, when [plan_cache_mode](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE_MODE) is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.

This heuristic can be overridden, forcing the server to use either generic or custom plans, by setting plan_cache_mode to force_generic_plan or force_custom_plan respectively. This setting is primarily useful if the generic plan's cost estimate is badly off for some reason, allowing it to be chosen even though its actual cost is much more than that of a custom plan.```

[jtv]

I guess GC could become an issue. And, of course, lookup has to remain fast enough that there's still a gain to be had.

What if we had a server-side cache of plans, which the client can opt to use? It would create some room for the server to manage eviction.

[davecramer]

that is actually one of the ideas being tossed around. Notionally prepare would return a "query_id" which you could subsequently execute. We have to make sure that if the query_id doesn't exist it doesn't break a transaction so that you can re-prepare the statement

[jtv]

And what about just using the query text, no ID? Costlier, but simplifies management, and saves the extra roundtrip (and typically savepoint I guess!) on a miss.

[jtv]

Oh a little-talked-about possible benefit of server-side caching... the client can't easily tell, but the server can easily skip the whole caching thing if planning the query is fast.

[davecramer]

In order to keep this from creating an error when execute <query_id> does not exist we will keep this above the executor. We don't really want to involve the planner too much unless necessary. Also gets rid of the savepoint