Re: dynamically generated SQL and planner/performance

On Sat, 22 Mar 2008 14:56:28 -0300
Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> PL/pgSQL can present a query to the planner, and get a plan. This
> plan can be cached by PL/pgSQL. So if you execute a function
> twice, the first time PL/pgSQL caches the plan and passes it to the
> executor; the second time PL/pgSQL gets the plan from the cache and
> passes it to the executor.
>
> Except if you use plpgsql's EXECUTE: when you do that, PL/pgSQL
> does no caching at all for that query, and asks the planner for a
> new plan each time.

But does this cascade to all the nested functions?

functionA {
execute functionB {
sql statement
}
}

As my previous example I've a functionA that build up a statement that
call another functionB that just contain static sql statements.

Will the plan for the statements inside functionB be used if it is
called indirectly?

So the loss will depends only on how complicated is the statement
that get "execute"d.

If it's something like
execute select res fron functionB() into _res;
since the "complicated" part is inside each functionB I shouldn't
expect any great loss.

Is it?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it