Hola Daymel,
Gracias por contestar,
Me pasa que armo una función, la ejecuto 3 o 4 veces sin problema,
resuelve en menos de un segundo, barbaro pero... en un momento dado, a
veces la 5ta ejecución o la 6ta, pasa a demorar 5 minutos así de la nada
y ya no se recupera.
La recompilo, apenas agregando un espacio en blanco en el código, y se
repite el comportamiento. Así fue que buscando, llegue a esta parte de
la documentación y no se como salvarlo (por ahora voy como dice, con el
execute) pero no es algo que me termine por gustar como solución.
Por eso, la pregunta.
Saludos,
On 2019-02-08 15:41, Daymel Bonne wrote:
Hola Stephen:
El vie., 8 de feb. de 2019 a la(s) 13:34, Stephen Amell
(mrstephenam...@gmail.com <mailto:mrstephenam...@gmail.com>) escribió:
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?
¿Alternativa a que? Está claro cómo es que funciona la caché del plan
de ejecución en la documentación. Tienes algún problema en específico?
Saludos
--
Daymel Bonne
Database Consultant, Training & Services
2ndQuadrant - PostgreSQL Solutions for the Enterprise
https://www.2ndQuadrant.com/ <https://www.2ndquadrant.com/>
