From: Andrei Lepikhov <lepihov@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: premal.patel@veeva.com, pgsql-bugs@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: BUG #19076: Generic query plan is extremely slow
Date: Fri, 10 Oct 2025 11:56:24 +0200
Message-ID: <95b73780-94cc-496c-8658-1f71da4c8a71@gmail.com> (raw)
In-Reply-To: <CAApHDvrtYT_eoqgGtR-6Z=s0upNv1NeiptdRdEEnVm2=osD8jg@mail.gmail.com>
References: <19076-4d897c04a44a9627@postgresql.org>
<281ae4783e0174e1cb77a560936b86a9f542bba4.camel@cybertec.at>
<CAApHDvrtYT_eoqgGtR-6Z=s0upNv1NeiptdRdEEnVm2=osD8jg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: lepihov@gmail.com, dgrowleyml@gmail.com, laurenz.albe@cybertec.at
Subject: Re: BUG #19076: Generic query plan is extremely slow
In-Reply-To: <95b73780-94cc-496c-8658-1f71da4c8a71@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox