Thread: dynamically generated SQL and planner/performance

I've a lot of code that should call different functions according to
values in tables.

something like:


create table FuncName(Name varchar(10), arg int);

insert into FuncName values('ciro',5);
insert into FuncName values('pino',7);
insert into FuncName values('nano',11);

create or replace function ciro(arg int, out res int) as
$$
begin
  res:=arg;
  return;
end;
$$ language plpgsql;

create or replace function pino(arg int, out res int) as
$$
begin
  res:=arg*2;
  return;
end;
$$ language plpgsql;

create or replace function nano(arg int, out res int) as
$$
begin
  res:=arg*4;
  return;
end;
$$ language plpgsql;


create or replace function FBuilder(out res int) as
$$
declare
  statement varchar(256);
  _Name varchar(10);
  _arg int;
begin
  select into _Name, _arg Name, arg from FuncName order by random();
  statement := ' select * from ' || _Name || '(' || _arg || ')';
  execute statement into res;
  return;
end;
$$ language plpgsql;

select * from FBuilder();

I don't understand which impact will have on the planner, caching
etc... generating the statement dynamically.

Will the execution of ciro, pino e nano be affected?
Or will just the plan for executing FBuilder statement be affected?

thanks

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

Ivan Sergio Borgonovo wrote:
> I've a lot of code that should call different functions according to
> values in tables.
>
> something like:
>
>
> create table FuncName(Name varchar(10), arg int);
>
> insert into FuncName values('ciro',5);
> insert into FuncName values('pino',7);
> insert into FuncName values('nano',11);
>
> create or replace function ciro(arg int, out res int) as
> $$
> begin
>   res:=arg;
>   return;
> end;
> $$ language plpgsql;
>
> create or replace function pino(arg int, out res int) as
> $$
> begin
>   res:=arg*2;
>   return;
> end;
> $$ language plpgsql;
>
> create or replace function nano(arg int, out res int) as
> $$
> begin
>   res:=arg*4;
>   return;
> end;
> $$ language plpgsql;
>
>
> create or replace function FBuilder(out res int) as
> $$
> declare
>   statement varchar(256);
>   _Name varchar(10);
>   _arg int;
> begin
>   select into _Name, _arg Name, arg from FuncName order by random();
>   statement := ' select * from ' || _Name || '(' || _arg || ')';
>   execute statement into res;
>   return;
> end;
> $$ language plpgsql;
>
> select * from FBuilder();
>
> I don't understand which impact will have on the planner, caching
> etc... generating the statement dynamically.
>
> Will the execution of ciro, pino e nano be affected?
> Or will just the plan for executing FBuilder statement be affected?

The execution plan of dynamic queries from PL/pgSQL will not be cached;
the query will be prepared at execution time every time you execute it.

The plan for executing "select * from FBuilder()" will not be affected
by the SQL statements you execute from within FBuilder().
All you can do to hint at the planner that calling FBuilder() will be
expensive is (from version 8.3 on) to include a COST clause in the
CREATE FUNCTION statement.

Unless the dynamic statements are complicated or are called very often,
I would not worry too much about the additional cost of preparing the statement.

Yours,
Laurenz Albe

On Fri, 21 Mar 2008 12:15:19 +0100
"Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

> The execution plan of dynamic queries from PL/pgSQL will not be
> cached; the query will be prepared at execution time every time you
> execute it.

Pardon my ignorance but I really have a very vague idea of what the
planner does and how it works.

Does the planner "compose" plans or does it have one plan for each
statement?

if I've nested functions what does the planner?

When I do select * from func(); explain analyse doesn't say much.

> The plan for executing "select * from FBuilder()" will not be
> affected by the SQL statements you execute from within FBuilder().
> All you can do to hint at the planner that calling FBuilder() will
> be expensive is (from version 8.3 on) to include a COST clause in
> the CREATE FUNCTION statement.

> Unless the dynamic statements are complicated or are called very
> often, I would not worry too much about the additional cost of
> preparing the statement.

The generated statement is very similar to the one I posted.
I just have to call the right function according to what I find in a
table.

The work flow is something like:
- user write in a table a choice (int)
- each int is associated with a plpgsql function
- the statement is assembled, executed and the the specific function
result is returned in a way similar to the one of FBuilder

the dynamically assembled query is very similar to the one shown in
FBuilder.
There is a simple select that fetch the function name and an argument.
It is a bit more complicated than the one shown since the arguments
are taken from different tables and there is a where clause.

But nothing far from a:

[A]
select a.Name, b.arg from FuncTable a join Args b on b.id=a.id where
b.val>7;

then the statement is really as:

[B]
statement := ' select * from ' || _Name || '(' || _arg || ')';

Just I fetch a couple more args.

and then I actually do a

[C]
execute statement into res;
return;

Now I'd like what I'm loosing using dynamic queries.

Can I say that:
- I won't lose anything in step [A]
- I won't lose anything in step [B]
- I'll lose something in step [C]
- I won't lose anything in the actual execution of function _Name(...)

My main concern is _Name(...) _Name[s] functions are all made with
static statements but they are relatively critical since most use
aggregates, a bunch of join and sub-queries... so no matter if the
'select * from ' || _Name ...
is not optimised I'd like to know if the execution of what's inside
each _Name function will be.

thanks

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

Ivan Sergio Borgonovo wrote:
> On Fri, 21 Mar 2008 12:15:19 +0100
> "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:
>
> > The execution plan of dynamic queries from PL/pgSQL will not be
> > cached; the query will be prepared at execution time every time you
> > execute it.
>
> Pardon my ignorance but I really have a very vague idea of what the
> planner does and how it works.
>
> Does the planner "compose" plans or does it have one plan for each
> statement?

The planner takes a query and creates a plan.  Later, the executor takes
a plan and executes it, producing results.  For example you can create
plans with the PREPARE command, and later execute them with the EXECUTE
command (note that the EXECUTE SQL command is different from the EXECUTE
plpgsql command).

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.

Also, new in 8.3 is a facility for "plan invalidation", which means that
every time the server executes something that makes that plan stale, the
cache is dropped and the plan is rebuilt next time.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Sat, 22 Mar 2008 14:56:28 -0300
Alvaro Herrera <alvherre@commandprompt.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

Ivan Sergio Borgonovo wrote:

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

Each function is planned and cached independently, regardless of how
they are called.  In fact the previous sentence does not really make
sense, because what truly happens is that each _sentence_ within each
function is planned and cached independently.


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

Correct -- the part that's being discarded each time here is the SELECT
that calls up functionB, not functionB's statements themselves.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.