Message-ID: <7bbe7bf1fa86dc075d2b655fdadd08fa99180212.camel@cybertec.at>
Subject: Re: thousands of CachedPlan entry per backend
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "James Pang (chaolpan)" <chaolpan@cisco.com>, Pavel Stehule
	 <pavel.stehule@gmail.com>
Cc: "pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Date: Thu, 01 Jun 2023 14:48:23 +0200
In-Reply-To: 
 <PH0PR11MB51918A490F1EDE83C898DB44D6499@PH0PR11MB5191.namprd11.prod.outlook.com>
References: 
	<PH0PR11MB5191F9F3C8CA35737E6F0A99D6499@PH0PR11MB5191.namprd11.prod.outlook.com>
	 <9513916420a6c0a00eb5ecacfba984e2e5a97e0d.camel@cybertec.at>
	 <CAFj8pRDsJrJGL+nnKLb+1rM5P8gh_XT_bVmDK71k9VgTv8xfzA@mail.gmail.com>
	 <PH0PR11MB51918A490F1EDE83C898DB44D6499@PH0PR11MB5191.namprd11.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/7bbe7bf1fa86dc075d2b655fdadd08fa99180212.camel%40cybertec.at>
Precedence: bulk

On Thu, 2023-06-01 at 08:50 +0000, James Pang (chaolpan) wrote:
> we found thousands of cached plan , since JDBC driver only allow max 256 =
cached
> prepared statements, how backend cache so many sql plans. If we have one =
function,
> when application call that function will make backend to cache =C2=A0ever=
y SQL statement
> plan in that function too? =C2=A0=C2=A0and for table triggers, have simil=
ar caching behavior ?

Yes, as long as the functions are written in PL/pgSQL.
It only affects static SQL, that is, nothing that is run with EXECUTE.

Yours,
Laurenz Albe