Subject: Stored procedures

From: Simon Connah <[email-masked]>
Date: Sun, 27 Aug 2023 11:49:06 +0000

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 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 time 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?

Simon.

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 27 Aug 2023 07:15:28 -0700

On Sunday, August 27, 2023, Simon Connah <[email-masked]>
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 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 time
> it should be able to better optimise it.
>

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’t avoiding the parsing of the code
and are adding overhead that raw SQL doesn’t have.

David J.

---

From: Gurjeet Singh <[email-masked]>
Date: Sun, 27 Aug 2023 08:55:06 -0700

> Is that true? Does PostgreSQL use a JIT for stored procedures which make 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

---

From: Simon Connah <[email-masked]>
Date: Sun, 27 Aug 2023 17:02:21 +0000

------- Original Message -------
On Sunday, August 27th, 2023 at 16:55, Gurjeet Singh <[email-masked]> wrote:

> [email-masked] wrote:
> 

> http://Gurje.et

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