0

Edited to add more context

QUESTION: Does PostgreSQL "prepare" the statements stored inside functions and procedures? In other words, are the statements analysed planned when the function/procedure is created - or when it is called. If the statements inside the functions/procedures are analysed and planned each time the function is called, would it make sense for the client application to prepare the call of the function repeatedly?

I have a server-side function that returns data to my application. This function is called repeatedly by my application.

  1. The function does take parameters - 6 real values - and returns a table.
  2. Security is not an issue. It is a local application and the data is used only one user at a time.
  3. I am using postgresql due to its capacity of handling geometric data (points). I have billions of them and I need to query the table repeatedly.

Would it make sense from the performance side to prepare the call of this function and then keep reusing the prepared statement?

My question is related to the fact that I feel that PostgreSQL would already have the code of functions "prepared" thus it makes no sense to prepare it before.

Could you masters please share some words of explanation on this topic?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Rafael Scudelari de Macedo
  • 25
  • 6
  • 1
    https://ericlippert.com/2012/12/17/performance-rant/ – Frank Heikens Oct 21 '22 at 08:41
  • Will you provide some context please? Is the function called repeatedly from the logic tier? Does it have any arguments? In opinion it is usually a good idea to first prepare a statement and then execute it except when it is very simple and has no parameters. Have a look at [SQLi risks](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) too. – Stefanov.sm Oct 21 '22 at 08:51
  • @Stefanov.sm Thanks for your reply. I added context and improved the question. – Rafael Scudelari de Macedo Oct 21 '22 at 09:07
  • You may have a look [here](https://dba.stackexchange.com/questions/8119/function-performance) on how and when the statements within a function/procedure are planned. Anyway in your case I would use a prepared statement. The statement itself will be planned only once. And I would try to stay away from micro-optimization too. – Stefanov.sm Oct 21 '22 at 09:20
  • @Stefanov.sm Thanks, friend. I will likely drop the usage of the function and go to a prepared select, which will likely be easier for the engine to optimize by itself. – Rafael Scudelari de Macedo Oct 21 '22 at 09:26

1 Answers1

0

First, PostgreSQL will not create a prepared statement unless you run PREPARE. But I assume that you are not interested in prepared statements as such, but that you want to know whether the execution plan will be cached or not.

That depends on the kind of function and the statement. PostgreSQL will cache plans if

  • it is a prepared statement or a statement in a PL/pgSQL function (not for other procedural languages)

  • it is an INSERT, SELECT, UPDATE or DELETE statement

If the statements has any parameters (PL/pgSQL variables will become parameters), PostgreSQL uses a heuristics: for the first five executions, the statement is planned with the actual parameter values. After that, PostgreSQL decides whether using a generic plan (that does not take the parameters into account) will be fine, and from then on it can use a cached plan.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi @laurenz-albe, thanks for the reply. I am thinking of letting go of functions and procedures (I don't really need them) and preparing the statements.= to cache the execution plan. – Rafael Scudelari de Macedo Oct 21 '22 at 09:31