Re: Funciones y Plan Caching

2019-02-08 Thread Stephen Amell 

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 ) 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
 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/

Re: Funciones y Plan Caching

2019-02-08 Thread Daymel Bonne 
Hola Stephen:

El vie., 8 de feb. de 2019 a la(s) 13:34, Stephen Amell (
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
>  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/