Re: Prepared statements versus stored procedures

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.