Date: Sun, 27 Aug 2023 17:02:21 +0000
To: Gurjeet Singh <gurjeet@singh.im>
From: Simon Connah <simon.n.connah@protonmail.com>
Cc: pgsql-novice <pgsql-novice@lists.postgresql.org>
Subject: Re: Stored procedures
Message-ID: 
 <JdElbGSkSj6OhJbKZ80N-YvBiiP0hwuxUJalcENDTC2Pbli4LpTPR6oCaJMWGnJConEZ9dvQCv7Fr4EaLdEPkQcyQ6bCFU0Z5a92udTZAaA=@protonmail.com>
In-Reply-To: 
 <CABwTF4Wu0nMKQZVVUTzYJgxx64DRpgFnyX3qy=h=qcErEkSUPw@mail.gmail.com>
References: 
 <eXWbA7jWtEDZQJSHx3brQYIZX_tj1kEjvG3B1-Sye_0OaGrlBTBRWZ27b_lUuq2wjftN7LfdCbxWy-zfz1dYKXjckquiXaoLh_0CdaP96zI=@protonmail.com>
 <CABwTF4Wu0nMKQZVVUTzYJgxx64DRpgFnyX3qy=h=qcErEkSUPw@mail.gmail.com>
Feedback-ID: 24074989:user:proton
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
 micalg=pgp-sha512;
 boundary="------18f81a42ab13a8d37d438cc5794a67e56521206939d993202456dabb4efdb862";
 charset=utf-8
Archived-At: 
 <https://www.postgresql.org/message-id/JdElbGSkSj6OhJbKZ80N-YvBiiP0hwuxUJalcENDTC2Pbli4LpTPR6oCaJMWGnJConEZ9dvQCv7Fr4EaLdEPkQcyQ6bCFU0Z5a92udTZAaA%3D%40protonmail.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------18f81a42ab13a8d37d438cc5794a67e56521206939d993202456dabb4efdb862
Content-Type: multipart/mixed;boundary=---------------------fc3079d5ec59b50203c7a831ca7ec949

-----------------------fc3079d5ec59b50203c7a831ca7ec949
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;charset=utf-8


------- Original Message -------
On Sunday, August 27th, 2023 at 16:55, Gurjeet Singh <gurjeet@singh.im> wr=
ote:


> =


> =


> On Sun, Aug 27, 2023 at 4:49=E2=80=AFAM Simon Connah
> simon.n.connah@protonmail.com wrote:
> =


> > I'm building a website in Python using the Django framework and am usi=
ng the ORM at the moment but I can see a few situations 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 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 ma=
ke them faster than one-off queries?
> =


> =


> If your application logic involves many queries for one operation,
> queries that produce the result that's then massaged and used in
> subsequent queries, then yes, your application will benefit from
> bundling that logic in a function/stored procedure and execute purely
> on database server. This is because, as you noted, it reduces network
> round-trips. Hence it will lower your latency for that one operation.
> But also note that since the database is now doing most of the work,
> and because it's limited by the number of CPUs on the database server,
> this can lower your application's throughput; hence this is not
> advisable if you have many more, and always querying client
> connections than the number of CPUs on the server.
> =


> If you desire high throughput, and still wish to use functions to keep
> the single-operation latency low, and if your application's workload
> has a very high read:write ratio, you can split your read-only
> workload to use streaming replicas/standby servers, which you can have
> as many as you want (within reason :-).
> =


> Instead of, or in addition to functions, you may use CTE (aka WITH
> clause) to bundle many SQL commands into one.
> =


> PL/pgSQL is an interpreted language, not a compiled one. It caches and
> reuses the query plans of the SQL commands in the function, but that's
> about it, in terms of optimizations. Unlike a compiled language, it's
> not optimized to eliminate unnecessary operations, etc. As David
> notes, plpgsql has some overhead, as well, so, for example, wrapping
> single SQL statements in a plpgsql function will generally make the
> operation slower than executing that SQL directly.
> =


> Postgres does have JIT optimizations for expression evaluation, but
> that benefits all SQL commands, irrespective of whether they are
> directly coming from the client, or wrapped in a function.
> =


> Best regards,
> Gurjeet
> http://Gurje.et

Thank you both for your replies. That was really handy.
-----------------------fc3079d5ec59b50203c7a831ca7ec949--

--------18f81a42ab13a8d37d438cc5794a67e56521206939d993202456dabb4efdb862
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: ProtonMail

wnUEARYKACcFgmTrgZEJkFrvKC74ta6lFiEEXOYF9uqFRn4815bYWu8oLvi1
rqUAAO9UAQCyvlDzyfVR3Ior49haIRD6XVUdJBgTV5xwXVlyDAnHfwD9FkK9
5khhb5L3O7zTYBzyTGEzJLnVv9B1MQkq7/PfbAA=
=nJcg
-----END PGP SIGNATURE-----


--------18f81a42ab13a8d37d438cc5794a67e56521206939d993202456dabb4efdb862--