On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <lepi...@gmail.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
