You can read in detail about the different categories here.Ģ) Language name – SQL v/s PGPLSQL – If your function is nothing but a list of SQL Statements or a single SQL statement, mark the language as SQL and not PGPLSQL. VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. IMMUTABLE functions always return same value for a set of parameters (involves no DB lookups). Marking your function appropriately could be a very important performance tweak. ' USING a,b ġ) Function Volatility Categories – IMMUTABLE, STABLE, VOLATILE – The volatility category is a promise to the optimizer about the behavior of the function. I changed my function to look like this – CREATE OR REPLACE FUNCTION testfunction( IN a integer, IN b text) To replicate this behaviour, we can dynamically generate the SQL statements inside the functions and then EXECUTE them. When we execute a simple query, the plan of the query is determined at run time and is optimized as per the parameters used. The postgreSQL function was using a cached query plan which may not always be optimal for different parameter values. It turned out, the optimizer failed to optimize the execution plan according to the parameter values. Now, if the function was called 10 times from the report, my report would take 3-4 minutes to load!Īfter trying several things, converting the SQL statements in the function to dynamic SQL did the trick. If each of the queries was tested separately, outside the function, they executed in less than 1 sec and the function took about 20-30 secs.

What was observed is, the function got executed fast a couple of times and then the performance degraded. It looked something like this – CREATE OR REPLACE FUNCTION testfunction(IN integer, IN text) It had some IF ELSE conditions, which determined which of the query is to be executed. The postgreSQL function had 2 “select” queries which returned a resultset based on the various parameters that were passed to them. It was called several times in a loop with different parameters for a single report execution. I had a postgreSQL function which was getting executed from a BI Report. Helical Insight is the world’s best open source business intelligence tool.

Postgresql for loop 10 times