Re: Stored procedures

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


>

>

> On Sun, Aug 27, 2023 at 4:49 AM 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
situationswhere I might need to write raw SQL. My question is do stored procedures execute faster when compared to
queriesyou generate using Python and then send to PostgreSQL? I would assume they would as there is less communication
betweenthe two sides and also because PostgreSQL knows the stored procedure ahead of time it should be able to better
optimiseit. 
> >

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

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