MIME-Version: 1.0
References: 
 <CAK-MWwS_2UwF7XPy-8XpcVqUjEveDjDQRbXxPyzvJM+ooWeh9A@mail.gmail.com>
 <b7da2330-1054-4bb8-939a-cb05d377f70f@gmail.com>
 <CAK-MWwRP+OsZhEi5LGZcsWZikMj5+cCmVmK9oKgssFViF13NCQ@mail.gmail.com>
 <8480be46-6e2a-4b12-9f80-78ba3db79dfb@gmail.com>
In-Reply-To: <8480be46-6e2a-4b12-9f80-78ba3db79dfb@gmail.com>
From: Maxim Boguk <maxim.boguk@gmail.com>
Date: Mon, 12 May 2025 17:04:45 +0300
Message-ID: 
 <CAK-MWwSgP2_br2jHtZSWYEH-T9tDx73LxqkNez-0kmck_wk=xQ@mail.gmail.com>
Subject: Re: inefficient/wrong plan cache mode selection for queries with
 partitioned tables (postgresql 17)
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000095950a0634f0ce49"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK-MWwSgP2_br2jHtZSWYEH-T9tDx73LxqkNez-0kmck_wk%3DxQ%40mail.gmail.com>
Precedence: bulk

--00000000000095950a0634f0ce49
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, May 12, 2025 at 4:48=E2=80=AFPM Andrei Lepikhov <lepihov@gmail.com>=
 wrote:

> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning tim=
e
> > 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?


Such extension would be very useful (and in general - the solution based on
the actual execution data - seems more stable/predictable than the plan
cost based selection which is currently used by postgresql).


--=20
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

--00000000000095950a0634f0ce49
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:monospace,monospace;font-size:large"><br></div></div><br><div clas=
s=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_att=
r">On Mon, May 12, 2025 at 4:48=E2=80=AFPM Andrei Lepikhov &lt;<a href=3D"m=
ailto:lepihov@gmail.com">lepihov@gmail.com</a>&gt; wrote:<br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex">On 5/12/25 15:08, Maxim Boguk wro=
te:<br>
&gt; PS: problem not with difference between custom and generic planning ti=
me <br>
&gt; but with prepared statements<br>
&gt; generic plan plans only once, but custom plan plan every call (and pla=
n <br>
&gt; time cost 95% on total query runtime).<br>
Ah, now I got it.<br>
I&#39;m aware of this problem from at least two sources of regular complain=
ts.<br>
What can you do here? Let&#39;s imagine a palliative solution:<br>
Having pg_stat_statements data and the list of prepared statements (see <br=
>
pg_prepared_statement) and queryId enabled, there is a way to force a <br>
custom or generic plan in specific cases only: look up into min/max <br>
query execution time. If no big difference exists and planning time is <br>
sufficient, setting force_generic_plan for this plan makes sense. In <br>
another case, if the planning time is too short or the generic plan is <br>
unstable - switch to force_custom_plan.<br>
<br>
It is not hard to write such a tiny extension. As I see, the only extra <br=
>
stored &quot;C&quot; procedure is needed to set up force-plan-type flag emp=
loying <br>
FetchPreparedStatement(). The rest of the code - querying <br>
pg_stat_statements and switching between plan types may be written in <br>
plpgsql.<br>
<br>
If I&#39;m not mistaken, it will work with all PG versions that are <br>
currently in support. What do you think?</blockquote><div><br></div><div><d=
iv style=3D"font-family:monospace,monospace;font-size:large" class=3D"gmail=
_default">Such extension would be very useful (and in general - the solutio=
n based on the actual execution data - seems more stable/predictable than t=
he plan cost based selection which is currently used by postgresql).</div><=
br></div><div>=C2=A0</div></div><span class=3D"gmail_signature_prefix">-- <=
/span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>=
<div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=
=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><span style=3D"font-fa=
mily:monospace,monospace">Maxim Boguk<br>Senior Postgresql DBA<br><br>Phone=
 UA: +380 99 143 0000<br>Phone AU: +61=C2=A0 45 218 5678<br></span><br></di=
v></div></div></div></div></div></div></div></div></div></div></div></div><=
/div></div>

--00000000000095950a0634f0ce49--