On Sun, 2023-11-19 at 17:30 +0000, Simon Connah wrote: > I was reading about prepared statements and how they allow the server to > plan the query in advance so that if you execute that query multiple times > it gets sped up as the database has already done the planning work. > > My question is this. If I make a stored procedure doesn't the database > already pre-plan and optimise the query because it has access to the whole > query? Or could I create a stored procedure and then turn it into a prepared > statement for more speed? I was also thinking a stored procedure would help > as it requires less network round trips as the query is already on the server.
Statements in functions and procedures don't get planned until the function or procedure is called for the first time. These plans don't get cached unless the procedural language you are using has special support for that.
Currently, only functions and procedures written in PL/pgSQL cache execution plans of static SQL statements. And you are right, that is usually a good thing.
Adding to this,
Stored procedures and functions can provide really dramatic speedups by eliminating round trips between statements where with traditional programming approaches you have to bring all the data back to the client side just to transform, run logic, etc, only to send it back to the database. How much benefit this provides is really situation specific, but can be impactful in many common situations.
Also, they provide the benefit of hiding schema details and providing a "database API" in situations where you want the application contract to the database to be written against the query output (perhaps in json) vs the schema. This pattern is controversial in some circles but I employ it often and it runs well. It can also be comforting not to rely on client side code to properly frame up the transaction.
This is only touching the surface -- there are many, many advantages to server side programming and it is a tremendously valuable skill to learn and master. The main downside, of course, is that postgres server programming can only be used in postgres without modification.
