On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <lepihov@xxxxxxxxx> 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
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
