Search code examples
postgresqlprepared-statementpostgresql-performance

Postgresql: Prepare statement - Function

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?

Solution

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