Re: Function execution costs 'n all that

Tom Lane wrote:
> Would a simple constant value be workable, or do we need some more
> complex model (and if so what)?

Consider:

ANALYZE myfunc(integer) ON (SELECT myfunc(7)) WITH RATIO 0.03;

ANALYZE myfunc(text,text) ON (SELECT myfunc(mt.a,mt.b) FROM mytable mt) WITH 
RATIO 1.071;

ANALYZE myfunc(text,text) ON (  SELECT myfunc(mt.a,mt.b) FROM mytable mt  UNION  SELECT myfunc(ot.a,ot.b) FROM
othertableot
 
) WITH RATIO 0.5;

These commands could turn on function timing for the lifespan of the query, with 
statistics gathered about the given function's runtimes.  The "WITH RATIO" 
clause would be there to translate runtimes (in milliseconds) into units of 
cpu_operator_cost.  The "WITH RATIO" clause could be optional, with a default 
ratio taken from the postgresql.conf file, if any exists, and finally defaulting 
to a hardcoded "reasonable" value.  Users would be well advised to adopt a 
consistent policy regarding system load at the time that various analyze 
functions are run.

If the function has side effects, it would be the user's responsibility to not 
analyze the function unless those side effects are acceptable.  The user can 
only analyze those queries that the user has permissions to run, so there 
shouldn't be any additional ability to generate side-effects beyond what the 
user already has permission to do.

The syntax might need some adjusting to make the parser happy and to avoid new 
reserved words.  The syntax used above is just an example.

It seems to me that the above system would work perfectly well for collecting 
the number of rows returned from a set returning function, not just the run times.

mark