MIME-Version: 1.0
References: <8216639.NyiUUSuA9g@aivenlaptop> <3084885.1675783797@sss.pgh.pa.us>
 <b69515864a7021505340f85476dc111e@postgrespro.ru> <CAPpHfdue9RVJKo33jKwrCeEXec0BTTH7CTxVcyXGMbfMExaGLA@mail.gmail.com>
 <758251661a3e8760ff52dd5c672a7505@postgrespro.ru> <CAFj8pRCoR9B+Ap70TQqWUa0PeeEk94D6VW20YbWN2Pm4b6-JMQ@mail.gmail.com>
 <30f02ca545915627905fbe3f23d93ec1@postgrespro.ru> <2275997.1737138456@sss.pgh.pa.us>
 <ad7296dd981c5f8eb76ff129913a55fd@postgrespro.ru> <51ebc5f3b846ce279b75ac4f5e5ac33f@postgrespro.ru>
 <CAFj8pRD2Fa84HoH_R_S5aKngyA6a+Au7U6NyidfBMrwEjFAc9g@mail.gmail.com>
 <401652.1738598448@sss.pgh.pa.us> <CAFj8pRC_4gEfmDoB5KbacNDdt3e6Tnf1z1BFK=MVp3+2kcDVqw@mail.gmail.com>
In-Reply-To: <CAFj8pRC_4gEfmDoB5KbacNDdt3e6Tnf1z1BFK=MVp3+2kcDVqw@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 3 Feb 2025 18:55:48 +0100
Message-ID: <CAFj8pRA7MFTfUPwDDWtghaUvAjbrMHFUPbHyLSX3h8+FDw4WFg@mail.gmail.com>
Subject: Re: SQLFunctionCache and generic plans
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>, Alexander Korotkov <aekorotkov@gmail.com>, 
	pgsql-hackers@lists.postgresql.org, Ronan Dunklau <ronan.dunklau@aiven.io>
Content-Type: multipart/alternative; boundary="0000000000005e7177062d409ccd"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRA7MFTfUPwDDWtghaUvAjbrMHFUPbHyLSX3h8%2BFDw4WFg%40mail.gmail.com>
Precedence: bulk

--0000000000005e7177062d409ccd
Content-Type: text/plain; charset="UTF-8"

Hi

I did multiple benchmarking, and still looks so the proposed patch doesn't
help and has significant overhead

testcase:

create or replace function fx(int) returns int as $$ select $1 + $1; $$
language sql immutable;
create or replace function fx2(int) returns int as $$ select 2 * $1; $$
language sql immutable;

I tested

do $$
begin
  for i in 1..1000000 loop
    perform fx((random()*100)::int); -- or fx2
  end loop;
end;
$$;

Results (master, patched):
fx: 17067 ms, 22165 ms
fx2: 2234 ms, 2311 ms

the execution of dynamic sql

2025-02-03 18:47:33) postgres=# do $$
begin
  for i in 1..1000000 loop
    execute 'select $1 + $1' using (random()*100)::int;
  end loop;
end;
$$;
DO
Time: 13412.990 ms (00:13.413)

In the profiler I see a significant overhead of the parser, so it looks
like there is some more (overhead), but plan cache is not used.

Please, can somebody recheck my tests?

Regards

Pavel

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

<div dir=3D"ltr"><div>Hi</div><div><br></div><div>I did multiple benchmarki=
ng, and still looks so the proposed patch doesn&#39;t help and has signific=
ant overhead</div><div><br></div><div>testcase:</div><div><br></div><div><d=
iv>create or replace function fx(int) returns int as $$ select $1 + $1; $$ =
language sql immutable;</div></div><div>create or replace function fx2(int)=
 returns int as $$ select 2 * $1; $$ language sql immutable;</div><div><br>=
</div><div>I tested</div><div><br></div><div>do $$ =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 <br>begin<br>=C2=A0 for i in 1..1000000 loop<br>=
=C2=A0 =C2=A0 perform fx((random()*100)::int); -- or fx2<br>=C2=A0 end loop=
;<br>end;<br>$$;</div><div><br></div><div>Results (master, patched):</div><=
div>fx: 17067 ms, 22165 ms</div><div>fx2: 2234 ms, 2311 ms</div><div><br></=
div><div>the execution of dynamic sql</div><div><br></div><div>2025-02-03 1=
8:47:33) postgres=3D# do $$<br>begin<br>=C2=A0 for i in 1..1000000 loop<br>=
=C2=A0 =C2=A0 execute &#39;select $1 + $1&#39; using (random()*100)::int;<b=
r>=C2=A0 end loop;<br>end;<br>$$;<br>DO<br>Time: 13412.990 ms (00:13.413)</=
div><div><br></div><div>In the profiler I see a significant overhead of the=
 parser, so it looks like there is some more (overhead), but plan cache is =
not used.</div><div><br></div><div>Please, can somebody recheck my tests?</=
div><div><br></div><div>Regards</div><div><br></div><div>Pavel<br></div><di=
v><br></div><div><br></div><div><br></div><div><br></div></div>

--0000000000005e7177062d409ccd--