Re: dynamically generated SQL and planner/performance

On Fri, 21 Mar 2008 12:15:19 +0100
"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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