MIME-Version: 1.0
References: <da4a01d8-ba4e-4752-add4-531cea0e0ac4@gmail.com> <CANzqJaCazZXsg4UuJhLOf8KC_1+bZLpA35R-MHA2n4Muky9Q0w@mail.gmail.com>
In-Reply-To: <CANzqJaCazZXsg4UuJhLOf8KC_1+bZLpA35R-MHA2n4Muky9Q0w@mail.gmail.com>
From: Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
Date: Wed, 10 Jul 2024 16:37:01 -0400
Message-ID: <CAHbZ42yitXv_73f1HpCK79g6AeGdpgNJfmQ52JAQmxVU_G9FJg@mail.gmail.com>
Subject: Re: can stored procedures with computational sql queries improve API performance?
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006f549b061cea9c66"
Archived-At: <https://www.postgresql.org/message-id/CAHbZ42yitXv_73f1HpCK79g6AeGdpgNJfmQ52JAQmxVU_G9FJg%40mail.gmail.com>
Precedence: bulk

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

Great tip!!! Thx

El mi=C3=A9, 10 de jul de 2024, 16:17, Ron Johnson <ronljohnsonjr@gmail.com=
>
escribi=C3=B3:

> On Tue, Jul 9, 2024 at 8:58=E2=80=AFPM Krishnakant Mane <kkproghub@gmail.=
com>
> wrote:
>
>> Hello.
>>
>> I have a straight forward question, but I am just trying to analyze the
>> specifics.
>>
>> So I have a set of queries depending on each other in a sequence to
>> compute some results for generating financial report.
>>
>> It involves summing up some amounts from tuns or of rows and also on
>> certain conditions it categorizes the amounts into types (aka Debit
>> Balance, Credit balance etc).
>>
>> There are at least 6 queries in this sequence and apart from 4 input
>> parameters. these queries never change.
>>
>> So will I get any performance benefit by having them in a stored
>> procedure rather than sending the queries from my Python based API?
>
>
> One problem is that the query planner reverts to a generic query plan if
> you execute the same query over and over in a loop in the SP.
>
> That bit us once.  A big SP that had been running "normally" for months
> suddenly went from about 20 minutes to six hours.  The solution (given by
> someone on this list a couple of years ago) was to add "set plan_cache_mo=
de
> =3D force_custom_plan;" above the call.
>
> That way, the query plan was updated every time.  Performance dropped to
> about 8 minutes IIRC.
>
>

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

<p dir=3D"ltr">Great tip!!! Thx</p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">El mi=
=C3=A9, 10 de jul de 2024, 16:17, Ron Johnson &lt;<a href=3D"mailto:ronljoh=
nsonjr@gmail.com">ronljohnsonjr@gmail.com</a>&gt; escribi=C3=B3:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px=
 #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Tue, Jul=
 9, 2024 at 8:58=E2=80=AFPM Krishnakant Mane &lt;<a href=3D"mailto:kkproghu=
b@gmail.com" target=3D"_blank" rel=3D"noreferrer">kkproghub@gmail.com</a>&g=
t; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">Hello.<br>
<br>
I have a straight forward question, but I am just trying to analyze the <br=
>
specifics.<br>
<br>
So I have a set of queries depending on each other in a sequence to <br>
compute some results for generating financial report.<br>
<br>
It involves summing up some amounts from tuns or of rows and also on <br>
certain conditions it categorizes the amounts into types (aka Debit <br>
Balance, Credit balance etc).<br>
<br>
There are at least 6 queries in this sequence and apart from 4 input <br>
parameters. these queries never change.<br>
<br>
So will I get any performance benefit by having them in a stored <br>
procedure rather than sending the queries from my Python based API?</blockq=
uote><div><br></div><div>One problem is that the query planner reverts to a=
 generic query plan if you execute the same query over and over in a loop i=
n the SP.</div><div><br></div><div>That bit us once.=C2=A0 A big SP that ha=
d been running &quot;normally&quot; for months suddenly went from about 20 =
minutes to six hours.=C2=A0 The solution (given by someone on this list a c=
ouple of years ago) was to add &quot;set plan_cache_mode =3D force_custom_p=
lan;&quot; above the call.</div><div><br></div><div>That way, the query pla=
n was updated every time.=C2=A0 Performance dropped to about 8 minutes IIRC=
.</div><div><br></div></div></div>
</blockquote></div>

--0000000000006f549b061cea9c66--