MIME-Version: 1.0
References: 
 <eXWbA7jWtEDZQJSHx3brQYIZX_tj1kEjvG3B1-Sye_0OaGrlBTBRWZ27b_lUuq2wjftN7LfdCbxWy-zfz1dYKXjckquiXaoLh_0CdaP96zI=@protonmail.com>
In-Reply-To: 
 <eXWbA7jWtEDZQJSHx3brQYIZX_tj1kEjvG3B1-Sye_0OaGrlBTBRWZ27b_lUuq2wjftN7LfdCbxWy-zfz1dYKXjckquiXaoLh_0CdaP96zI=@protonmail.com>
From: Gurjeet Singh <gurjeet@singh.im>
Date: Sun, 27 Aug 2023 08:55:06 -0700
Message-ID: 
 <CABwTF4Wu0nMKQZVVUTzYJgxx64DRpgFnyX3qy=h=qcErEkSUPw@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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABwTF4Wu0nMKQZVVUTzYJgxx64DRpgFnyX3qy%3Dh%3DqcErEkSUPw%40mail.gmail.com>
Precedence: bulk

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 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 comp=
ared to queries you generate using Python and then send to PostgreSQL? I wo=
uld assume they would as there is less communication between the two sides =
and also because PostgreSQL knows the stored procedure ahead of time it sho=
uld 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?

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