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

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?

--
regards, Andrei Lepikhov