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