MIME-Version: 1.0
References: <CAB+=1TWW+iPTtH-P+-b8CdS0fPvt2+8B7OQ5gS9ostuoPvMJqg@mail.gmail.com>
In-Reply-To: <CAB+=1TWW+iPTtH-P+-b8CdS0fPvt2+8B7OQ5gS9ostuoPvMJqg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 4 Sep 2025 19:21:07 -0400
Message-ID: <CANzqJaB_1B0YCMCLnY5-y3AS2jcTyzOftc3d9bs6G0On9zeTBg@mail.gmail.com>
Subject: Re: Debugging query performance in postgres
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000721b63063e01fa3a"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaB_1B0YCMCLnY5-y3AS2jcTyzOftc3d9bs6G0On9zeTBg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Sep 4, 2025 at 12:58=E2=80=AFPM veem v <veema0000@gmail.com> wrote:

> Hello,
> We have a situation in which we had a dml query within a procedure that
> was running fine but suddenly the plan flipped and it started running
> longer.
>

That sounds like something that bit me once.  Took a while to figure out,
and was very puzzling.

This list explained the problem and gave me the solution.  If you run the
same query multiple times in, after five executions, the PG query optimizer
decides to use a generic plan.  This disables that choice to use a generic
plan:

set plan_cache_mode =3D force_custom_plan;

Setting that returned the procedures to their previously-rapid execution.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Sep 4, 2025 at 12:58=E2=80=AFPM v=
eem v &lt;<a href=3D"mailto:veema0000@gmail.com">veema0000@gmail.com</a>&gt=
; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello,<br>We have a =
situation in which we had a dml query within a procedure that was running f=
ine but suddenly the plan flipped and it started running longer.</div></blo=
ckquote><div>=C2=A0</div></div><div>That sounds=C2=A0like something that bi=
t me once.=C2=A0 Took a while to figure out, and was very puzzling.</div><d=
iv><br></div><div>This list explained the problem and gave me the solution.=
=C2=A0 If you run the same query multiple times in, after five executions, =
the PG query optimizer decides to use a generic plan.=C2=A0 This disables t=
hat choice to use a generic plan:</div><div><br></div><div>set plan_cache_m=
ode =3D force_custom_plan;</div><div><br></div><div>Setting that returned t=
he procedures to their previously-rapid execution.</div><div><br></div><spa=
n class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"=
gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sau=
ce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&g=
t; lobster!</div></div></div></div></div></div>

--000000000000721b63063e01fa3a--