Hola Lista,
Les escribo para preguntarles si alguien noto esto a la hora de tener
mal rendimiento en funciones:
41.10.2. Plan Caching
The PL/pgSQL interpreter parses the function's source text and produces
an internal binary instruction tree the first time the function is
called (within each session). The instruction tree fully translates the
PL/pgSQL statement structure, but individual SQL expressions and
SQL commands used in the function are not translated immediately.
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. A disadvantage is that
errors in a specific expression or command cannot be detected until that
part of the function is reached in execution. (Trivial syntax errors
will be detected during the initial parsing pass, but anything deeper
will not be detected until execution.)
PL/pgSQL (or more precisely, the SPI manager) can furthermore attempt to
cache the execution plan associated with any particular prepared
statement. If a cached plan is not used, then a fresh execution plan is
generated on each visit to the statement, and the current parameter
values (that is, PL/pgSQL variable values) can be used to optimize the
selected plan. If the statement has no parameters, or is executed many
times, the SPI manager will consider creating a /generic/ plan that is
not dependent on specific parameter values, and caching that for re-use.
T*ypically this will happen only if the execution plan is not very
sensitive to the values of the PL/pgSQL variables referenced in it. If
it is, generating a plan each time is a net win. See PREPARE
<https://www.postgresql.org/docs/9.6/sql-prepare.html> for more
information about the behavior of prepared statements.*
*Because PL/pgSQL saves prepared statements and sometimes execution
plans in this way, SQL commands that appear directly in a
PL/pgSQL function must refer to the same tables and columns on every
execution; that is, you cannot use a parameter as the name of a table or
column in an SQL command. To get around this restriction, you can
construct dynamic commands using the PL/pgSQL EXECUTE statement — at the
price of performing new parse analysis and constructing a new execution
plan on every execution.*
The mutable nature of record variables presents another problem in this
connection. When fields of a record variable are used in expressions or
statements, the data types of the fields must not change from one call
of the function to the next, since each expression will be analyzed
using the data type that is present when the expression is first
reached. EXECUTE can be used to get around this problem when necessary.
https://www.postgresql.org/docs/9.6/plpgsql-implementation.html
Mas alla del execute que menciona, se sabe de alguna alternativa,
¿quizás los nuevos stored procedures?
