Re: BUG #19076: Generic query plan is extremely slow

On 8/10/2025 11:13, David Rowley wrote:
> On Wed, 8 Oct 2025 at 21:21, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>> Either don't use a prepared statement for this statement, or make sure
>> that "plan_cache_mode" is set to "force_custom_plan" for that single
>> prepared statement.We already have a couple of proposals that would alleviate this
recurring issue.
The first one [1] introduces 'referenced' planning for generic plans.
There, the optimiser may use current values for incoming parameters to
predict estimations, rather than using magic constants like 0.3 and
0.005, which seem too conservative in many cases. To my knowledge, SQL
Server, Oracle, and some other systems employ this approach. Although
not the ideal solution, it is optional and may help people choose a
proper planning strategy.

The second feature [2] is an addition to the first one. It slightly
changes the behaviour of plan_cache_mode: it prioritises the strategy
declared in the CachedPlanSource::cursor_options over plan_cache_mode.
It provides users with the option to select a specific query and set a
generic/custom/auto strategy to mitigate severe performance degradation.

With later changes in the master branch, these features open the door
for extension developers to introduce 'execution-based' metrics and
choose a plan type for a specific plan cache entry.

[1]
https://www.postgresql.org/message-id/19919494-92a8-4905-a250-6cf17b89f7c3@gmail.com
[2]
https://www.postgresql.org/message-id/458ace73-4827-43e1-8a30-734a93d4720f%40gmail.com

--
regards,
Andrei Lepikhov,
pgEdge