MIME-Version: 1.0
References: 
 <CACoKFYRSOZFA0m33BCDHBkget30sY71hMwH9peHvTKxcV6Ha4A@mail.gmail.com>
 <1761501.1752803865@sss.pgh.pa.us>
 <CACoKFYQLSkeFwytFFaKXQBo3U79zTEoe5z6t24dTGEuKcpb9-Q@mail.gmail.com>
 <1774625.1752808221@sss.pgh.pa.us>
In-Reply-To: <1774625.1752808221@sss.pgh.pa.us>
From: Jerry Brenner <jbrenner@guidewire.com>
Date: Fri, 18 Jul 2025 06:20:03 -0700
Message-ID: 
 <CACoKFYQBVOmU3gRvmYhvBzSFTef02eMbn6ejxeR0sf5vSzbn4g@mail.gmail.com>
Subject: Re: Is there a way to identify a plan generated by GECO?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f1abc3063a33fe5d"
Archived-At: 
 <https://www.postgresql.org/message-id/CACoKFYQBVOmU3gRvmYhvBzSFTef02eMbn6ejxeR0sf5vSzbn4g%40mail.gmail.com>
Precedence: bulk

--000000000000f1abc3063a33fe5d
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

We just jdbc and bind variables, so we are using PreparedStatements.
plan_cache_mode is set to auto

So, it sounds like there could be plan caching. (I wasn't aware of that.)
Is there any kind of running counter in a system view that tracks the
number of executions of cached plans?
We are capturing the plans via auto_explain and limited to the explain
options available with that path. Is there anything in the plan that would
tell us if the execution used a cached plan? (My manual explains does not
use a prepare.)

Thanks, Jerry

On Thu, Jul 17, 2025 at 8:10=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jerry Brenner <jbrenner@guidewire.com> writes:
> > I don't have any background with the randomized search.  Does the
> repeated
> > pattern with the same plan being executed multiple times in a time rang=
e
> > and then the plan changes, never to change back, match the expectation
> with
> > the randomization?
>
> [ shrug... ]  Insufficient information.  There could be some plan
> caching going on that contributes to this effect, though.
>
>                         regards, tom lane
>
>

--000000000000f1abc3063a33fe5d
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">We just jdbc and bind variables, so we are using PreparedS=
tatements.<div>plan_cache_mode is set to auto</div><div><br></div><div>So, =
it sounds like there could be plan caching. (I wasn&#39;t aware of that.)=
=C2=A0=C2=A0</div><div>Is there any kind of running counter in a system vie=
w that tracks the number of executions of cached plans?</div><div>We are ca=
pturing the plans via auto_explain and limited to the explain options avail=
able with that path. Is there anything in the plan that would tell us if th=
e execution used a cached plan? (My manual explains does not use a prepare.=
)</div><div><br></div><div>Thanks, Jerry</div></div><br><div class=3D"gmail=
_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu,=
 Jul 17, 2025 at 8:10=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh=
.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex">Jerry Brenner &lt;<a href=3D"mailto:jbrenner@guide=
wire.com" target=3D"_blank">jbrenner@guidewire.com</a>&gt; writes:<br>
&gt; I don&#39;t have any background with the randomized search.=C2=A0 Does=
 the repeated<br>
&gt; pattern with the same plan being executed multiple times in a time ran=
ge<br>
&gt; and then the plan changes, never to change back, match the expectation=
 with<br>
&gt; the randomization?<br>
<br>
[ shrug... ]=C2=A0 Insufficient information.=C2=A0 There could be some plan=
<br>
caching going on that contributes to this effect, though.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
</blockquote></div>

--000000000000f1abc3063a33fe5d--