Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)

On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:

> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning time
> > but with prepared statements
> > generic plan plans only once, but custom plan plan every call (and plan
> > time cost 95% on total query runtime).
> Ah, now I got it.
> I'm aware of this problem from at least two sources of regular complaints.
> What can you do here? Let's imagine a palliative solution:
> Having pg_stat_statements data and the list of prepared statements (see
> pg_prepared_statement) and queryId enabled, there is a way to force a
> custom or generic plan in specific cases only: look up into min/max
> query execution time. If no big difference exists and planning time is
> sufficient, setting force_generic_plan for this plan makes sense. In
> another case, if the planning time is too short or the generic plan is
> unstable - switch to force_custom_plan.
>
> It is not hard to write such a tiny extension. As I see, the only extra
> stored "C" procedure is needed to set up force-plan-type flag employing
> FetchPreparedStatement(). The rest of the code - querying
> pg_stat_statements and switching between plan types may be written in
> plpgsql.
>
> If I'm not mistaken, it will work with all PG versions that are
> currently in support. What do you think?

But a more general question - this exact issue will affect every prepared
query logic which selects only a subset of partitions.
In this case - current logic will always select custom plan over generic
plan (even in case the both plans are actually the same).
E.g. If a fast/cheap query over a partitioned table has conditions that
allow use of only a few partitions - custom plan always wins whatever
database settings is (outside of force_custom_plan hammer).
Seems there could be something done about the cost calculation of generic
plan.

--
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678