Re: Funciones y Plan Caching

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
> (mrstephenamell(at)gmail(dot)com <mailto:mrstephenamell(at)gmail(dot)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/>
>