Big performance increase for PostgreSQL 12+

[feld]

If we can apply parameters: [plan_cache_mode: "force_custom_plan"] only when PostgreSQL 12+ servers are being connected to we will get a big performance increase for all Ecto/Postgrex users.

https://blog.soykaf.com/post/postgresql-elixir-troubles/

[josevalim]

Hi @feld, thank you for the heads up. I would be worried about setting this option for everyone upfront, but we can at least document it. Can you please send a PR either to Postgrex or to Postgrex adapter under EctoSQL (or both) with the steps for this configuration so people can try it out? Thank you!

[feld]

We have more information that would be great to have documented:

PostgreSQL 11 and older:

prepare: :unnamed

PostgreSQL 12 and newer:

prepare: :named,
parameters: [plan_cache_mode: "force_custom_plan"]

[richardkmichael]

I'm investigating an Ecto performance problem right now, so I'd be happy to add to documentation. (Aside, in my case, plan_cache_mode has no effect.)

Though, changing the planner for the entire session seems a fairly drastic adjustment.

From the Postgres commit log:

This can be used to work around performance problems in specific cases. Note, however, that the plan cache behavior is subject to change, so this setting, like all settings that force the planner's hand, should be reevaluated regularly.

And FWIW, Brandstetter on DBA.SO:

Postgres 12 introduces the additional server variable plan_cache_mode to force generic or custom plans. For special cases, use with care.

I suggest encouraging people to experiment before turning this on and are bitten later having forgotten about it. For example, per-session in psql:

SET plan_cache_mode = 'force_[generic|custom]_plan';

In contrast to that blog post (which recommends turning it on unless you know what you're doing), I would expect to turn this on only if you know the performance issue you are addressing, why the planner is inadequate in that case, and you've tried and/or can't help it with changes to statistics. (A nice example about adjusting planner statistics. )

[feld]

The problem we see in Pleroma is with Postgres and prepared statements, which are all Ecto generates. Performance is good for the first 5 queries and then becomes terrible.

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

[mathiasose]

I tested this now (on pg13 with pgBouncer) and got ** (Postgrex.Error) ERROR 08P01 (protocol_violation) unsupported startup parameter: plan_cache_mode". We are having some performance issues after upgrading from pg11 to pg13, so I was looking for optimizations. Has there been any more developments on this issue recently?

[michallepicki]

My related story: https://elixirforum.com/t/ecto-query-often-timing-out-when-using-a-pin-next-to-module-attribute/40640

I think an option to disable prepared statements on per-query basis would be useful.

[feld]

This is being actively discussed on the Postgres mailing list as someone in the Go community hit the same issue and opened a bug report about the performance of the query planner with parameterized statements.

There is one useful suggestion that Postgrex may be able to follow up on:

Yep, and the core project pretty much says that if you don't like this you need to complain to the driver writer and ask them to provide you an interface to the unnamed parse-bind-execute API which lets you perform parameterization without memory, just safety.

source: https://www.postgresql.org/message-id/CAKFQuwZDyjnCRGfvbQJrhfOiCx5z5%3DkhZr4QJWUMEcEcwx4s9A%40mail.gmail.com

[greg-rychlewski]

We added some more info in the docs about how you can use unnamed prepared statements using this driver: https://github.com/elixir-ecto/postgrex/commit/f092eb79157302e3236b264fa635d1acf4356594