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

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

On Tue, Jul 9, 2024 at 8:58=E2=80=AFPM Krishnakant Mane <kkproghub@gmail.co=
m> 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_mode
=3D force_custom_plan;" above the call.

That way, the query plan was updated every time.  Performance dropped to
about 8 minutes IIRC.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Jul 9, 2024 at 8:58=E2=80=AFPM Kr=
ishnakant Mane &lt;<a href=3D"mailto:kkproghub@gmail.com">kkproghub@gmail.c=
om</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);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>

--0000000000008f4572061cea5663--