public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Maxim Boguk <[email protected]>
Cc: [email protected]
Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
Date: Mon, 12 May 2025 15:48:24 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAK-MWwRP+OsZhEi5LGZcsWZikMj5+cCmVmK9oKgssFViF13NCQ@mail.gmail.com>
References: <CAK-MWwS_2UwF7XPy-8XpcVqUjEveDjDQRbXxPyzvJM+ooWeh9A@mail.gmail.com>
<[email protected]>
<CAK-MWwRP+OsZhEi5LGZcsWZikMj5+cCmVmK9oKgssFViF13NCQ@mail.gmail.com>
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
view thread (16+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox