MIME-Version: 1.0
References: <1495292.1778939187@sss.pgh.pa.us>
 <02D2D7C5-7932-4030-B65A-4BECB645E9F0@gmail.com>
In-Reply-To: <02D2D7C5-7932-4030-B65A-4BECB645E9F0@gmail.com>
From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Date: Sat, 16 May 2026 17:31:49 +0300
Message-ID: 
 <CAB=Je-HxpyNNbn7girF6Mouf4gmzNQN=jdkrM49VwEtT_uFf4w@mail.gmail.com>
Subject: Re: Double prepare
To: Igor Korot <ikorot01@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>,
 Rob Sargent <robjsargent@gmail.com>
Content-Type: multipart/alternative; boundary="0000000000002f82760651f031b9"
Archived-At: 
 <https://www.postgresql.org/message-id/CAB%3DJe-HxpyNNbn7girF6Mouf4gmzNQN%3DjdkrM49VwEtT_uFf4w%40mail.gmail.com>
Precedence: bulk

--0000000000002f82760651f031b9
Content-Type: text/plain; charset="UTF-8"

>Might there be value in calling PQprepare as late as possible against most
up-to-dare data?
> Or is it better to run PQprepare for all known parameterized queries
> in the very beginning of the program and just execute them when needed?

In pgjdbc we maintain a client-side cache, so we don't have to prepare
everything in advance.
Frankly, I find it was a very helpful tool from the library perspective, as
it automatically optimized applications without
requiring application rebuild. The added prepared statement cache cut the
app server's response times from 1s to 0.5s (think of a generic enterprise
webpage).

Note that sql text is not a sufficient caching key: the same sql text might
have completely different
execution plans depending on the parameter types.

Note that statements prepare per-connection, so if you prepare everything
in advance, there might be noticeable overhead (cpu and memory)
if the specific connection uses only a few queries.

Vladimir

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

<div dir=3D"ltr"><div>&gt;Might there be value in calling PQprepare as late=
 as possible against most up-to-dare data?<br><div>&gt; Or is it better to =
run PQprepare for all known parameterized queries<br>&gt; in the very begin=
ning of the program and just execute them when needed?<br><br>In pgjdbc we =
maintain a client-side cache, so we don&#39;t have to prepare everything in=
 advance.<br>Frankly, I find it was=C2=A0a very helpful tool from the libra=
ry perspective, as it automatically optimized applications without<br>requi=
ring application rebuild. The added prepared statement cache cut the app se=
rver&#39;s response times from 1s to 0.5s (think of a generic enterprise we=
bpage).<br><br>Note that sql text is not a sufficient caching key: the same=
 sql text might have completely different</div><div>execution plans dependi=
ng on the parameter types.<br><br>Note that statements prepare per-connecti=
on, so if you prepare everything in advance, there might be noticeable over=
head (cpu and memory)</div><div>if the specific connection uses only a few =
queries.</div><br clear=3D"all"></div><div><div dir=3D"ltr" class=3D"gmail_=
signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div>Vladimi=
r</div></div></div></div><br></div>

--0000000000002f82760651f031b9--