Message-ID: <95b73780-94cc-496c-8658-1f71da4c8a71@gmail.com>
Date: Fri, 10 Oct 2025 11:56:24 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #19076: Generic query plan is extremely slow
To: David Rowley <dgrowleyml@gmail.com>
Cc: premal.patel@veeva.com, pgsql-bugs@lists.postgresql.org,
 Laurenz Albe <laurenz.albe@cybertec.at>
References: <19076-4d897c04a44a9627@postgresql.org>
 <281ae4783e0174e1cb77a560936b86a9f542bba4.camel@cybertec.at>
 <CAApHDvrtYT_eoqgGtR-6Z=s0upNv1NeiptdRdEEnVm2=osD8jg@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAApHDvrtYT_eoqgGtR-6Z=s0upNv1NeiptdRdEEnVm2=osD8jg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/95b73780-94cc-496c-8658-1f71da4c8a71%40gmail.com>
Precedence: bulk

On 8/10/2025 11:13, David Rowley wrote:
> On Wed, 8 Oct 2025 at 21:21, Laurenz Albe <laurenz.albe@cybertec.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