|
|
|
|
|
|
by chasers
1160 days ago
|
|
|
I will say that we are trying to keep the connection to the database open as long as possible and only disconnect when necessary. PgBouncer's db connections are more dynamic. So if Postgres is doing query plan caching by session then caches would be built up as that query hits other db connections. In theory, this should be better. |
|
|
A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)
By default (that is, when 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.
https://www.postgresql.org/docs/current/sql-prepare.html