MIME-Version: 1.0
In-Reply-To: 
 <eXWbA7jWtEDZQJSHx3brQYIZX_tj1kEjvG3B1-Sye_0OaGrlBTBRWZ27b_lUuq2wjftN7LfdCbxWy-zfz1dYKXjckquiXaoLh_0CdaP96zI=@protonmail.com>
References: 
 <eXWbA7jWtEDZQJSHx3brQYIZX_tj1kEjvG3B1-Sye_0OaGrlBTBRWZ27b_lUuq2wjftN7LfdCbxWy-zfz1dYKXjckquiXaoLh_0CdaP96zI=@protonmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sun, 27 Aug 2023 07:15:28 -0700
Message-ID: 
 <CAKFQuwa7agwXq=W+eY+52u=4HMYSviQJo=1OVB4HWwxQmmNhxQ@mail.gmail.com>
Subject: Re: Stored procedures
To: Simon Connah <simon.n.connah@protonmail.com>
Cc: pgsql-novice <pgsql-novice@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c0b5790603e835fc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwa7agwXq%3DW%2BeY%2B52u%3D4HMYSviQJo%3D1OVB4HWwxQmmNhxQ%40mail.gmail.com>
Precedence: bulk

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

On Sunday, August 27, 2023, Simon Connah <simon.n.connah@protonmail.com>
wrote:

> I'm building a website in Python using the Django framework and am using
> the ORM at the moment but I can see a few situations where I might need t=
o
> write raw SQL. My question is do stored procedures execute faster when
> compared to queries you generate using Python and then send to PostgreSQL=
?
> I would assume they would as there is less communication between the two
> sides and also because PostgreSQL knows the stored procedure ahead of tim=
e
> it should be able to better optimise it.
>
> Is that true? Does PostgreSQL use a JIT for stored procedures which make
> them faster than one-off queries?


Forget generalities.  Do what makes the most overall sense.  If a specific
case seems to need performance optimization then do both and measure.

If you are using plpgsql as the implementation language, or anything but
the atomic SQL format actually, you aren=E2=80=99t avoiding the parsing of =
the code
and are adding overhead that raw SQL doesn=E2=80=99t have.

David J.

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

On Sunday, August 27, 2023, Simon Connah &lt;<a href=3D"mailto:simon.n.conn=
ah@protonmail.com">simon.n.connah@protonmail.com</a>&gt; wrote:<br><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc s=
olid;padding-left:1ex">I&#39;m building a website in Python using the Djang=
o framework and am using the ORM at the moment but I can see a few situatio=
ns where I might need to write raw SQL. My question is do stored procedures=
 execute faster when compared to queries you generate using Python and then=
 send to PostgreSQL? I would assume they would as there is less communicati=
on between the two sides and also because PostgreSQL knows the stored proce=
dure ahead of time it should be able to better optimise it.<br>
<br>
Is that true? Does PostgreSQL use a JIT for stored procedures which make th=
em faster than one-off queries?</blockquote><div><br></div><div>Forget gene=
ralities.=C2=A0 Do what makes the most overall sense.=C2=A0 If a specific c=
ase seems to need performance optimization then do both and measure. =C2=A0=
</div><div><br></div><div>If you are using plpgsql as the implementation la=
nguage, or anything but the atomic SQL format actually, you aren=E2=80=99t =
avoiding the parsing of the code and are adding overhead that raw SQL doesn=
=E2=80=99t have.<br></div><div><br></div><div>David J.</div>

--000000000000c0b5790603e835fc--