Re: plpgsql functions organisation

On 05/02/2015 03:10 PM, Melvin Davidson wrote:
> Further to the point of saying functions are ineffiencent, consider the
> fact that as of the current version of PostgreSQL, plpgsql
> functions cannot be pre-optimized. So when they are referenced in a SQL
> statement, PostgreSQL (optimizer) has load the
> function from the catalogs, which involves overhead. If the function
> calls another function, then the process has to be repeated, which
> involves additional overhead. Ergo, that is not the most efficient way
> of doing things.
>

Yeah, I see the explanation here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

Which has this:

"As each expression and SQL command is first executed in the function,
the PL/pgSQL interpreter parses and analyzes the command to create a
prepared statement, using the SPI manager's SPI_prepare function.
Subsequent visits to that expression or command reuse the prepared
statement. Thus, a function with conditional code paths that are seldom
visited will never incur the overhead of analyzing those commands that
are never executed within the current session"

So it still not clear to me whether a monolithic function is better or
worse than one that calls other functions as needed. Probably over
thinking this, but it would make a good experiment. Just have to figure
out a realistic scenario to test. Thanks for the input.

--
Adrian Klaver
adrian.klaver@aklaver.com