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.
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.
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…