Disable prepared statements for an individual query

Ecto, through Postgrex and DB Connection, automatically uses prepared statements for all queries. To my knowledge the only way to disable prepared statements is to set prepare: :unnamed in the repo’s global configuration.

This question is prompted by a lingering issue in Oban, wherein a particular query gets dramatically slower once the prepared version of the query is used:

  • Have others ran into a similar problem?
  • Does anybody know a way to disable prepared statements for a specific query?
  • Did you work around the issue somehow?

My current thoughts for a workaround are to avoid using Repo.one or Repo.all entirely and instead generate a query, convert that to sql, run Repo.query directly, then convert the result using Repo.load. It isn’t too much work, but it isn’t intuitive and wouldn’t scale for multiple queries.

2 Likes

Wait what? On the Ecto side, the postgrex driver side, or the postgresql server side? o.O

Purely on the PG server side. There are a lot of details in the linked issue, but essentially after the fifth request using the prepared query PG’s planner decides to use a generic plan rather than a custom plan for mysterious internal reasons.

Full details are in this comment.

1 Like

Well that’s a fascinating read, wonder how postgresql’s planner is hitting such a bad case…

PostgreSQL’s docs have:

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.

I wonder if this could be used, supported by ecto somehow on a query specific basis…

I’m fairly sure those are specific to PG 12 (Oban is 9.6+), and they are global settings. It would be amazing to force a particular plan when preparing the query itself. Maybe it’ll get there one day.

I ended up going with the hack I mentioned in the first post. It doesn’t look too bad, and the performance is as good or better: https://github.com/sorentwo/oban/commit/756bb4f288bf052e040c8ef1eb5ceba4f2e744ed#diff-f3d97a16cc0b30c90038b8c0bce220b2R248

1 Like

I think I read blog post related to this problem https://blog.soykaf.com/post/postgresql-elixir-troubles

  • Use PostgreSQL 12, if possible
  • If you use Ecto, set plan_cache_mode to force_custom_plan in the PostgreSQL config
  • If you use PostgreSQL before version 12, set prepare: :unnamed in you Ecto database settings

I think that means when you are using PostgreSQL 12 you can fix this by adding parameters: [plan_cache_mode: "force_custom_plan"] to Ecto config.

Edit: fixed typo paramaters to parameters

2 Likes