Message-ID: <8480be46-6e2a-4b12-9f80-78ba3db79dfb@gmail.com>
Date: Mon, 12 May 2025 15:48:24 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: inefficient/wrong plan cache mode selection for queries with
 partitioned tables (postgresql 17)
To: Maxim Boguk <maxim.boguk@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
References: 
 <CAK-MWwS_2UwF7XPy-8XpcVqUjEveDjDQRbXxPyzvJM+ooWeh9A@mail.gmail.com>
 <b7da2330-1054-4bb8-939a-cb05d377f70f@gmail.com>
 <CAK-MWwRP+OsZhEi5LGZcsWZikMj5+cCmVmK9oKgssFViF13NCQ@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAK-MWwRP+OsZhEi5LGZcsWZikMj5+cCmVmK9oKgssFViF13NCQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/8480be46-6e2a-4b12-9f80-78ba3db79dfb%40gmail.com>
Precedence: bulk

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