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 othertable ot
) 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