Re: stored procedure stats in collector

On Fri, 2007-07-13 at 14:11 +0300, Martin Pihlak wrote:
> I'm working on a patch to extend the stats collector to handle stored procedure
> statistics (call counts, duration etc). The goal is to make this information
> visible via pg_stat functions/views. The collection would be controllable via
> "stats_function_level" GUC and will have minimal overhead when turned off. At
> our company we depend heavily on stored procedures and such a tool would be of
> great help. Perhaps others could also find it somewhat useful.

Very cool, certainly sounds like a useful feature to me.

> martinp=# select * from pg_stat_user_functions ;
>   procid | schemaname | procname | nargs | calls | total_time | total_cpu | self_time | self_cpu
> --------+------------+----------+-------+-------+------------+-----------+-----------+----------
>    16388 | public     | f1       |     0 |  4000 |      14978 |      8352 |     14978 |     8352
>    16389 | public     | f2       |     0 |  2000 |      40044 |      8364 |     25066 |       12
>    16390 | public     | f3       |     0 |  1000 |      40054 |      8364 |         9 |        0
> (3 rows)

(schemaname, procname, nargs) is still ambiguous in the face of function
overloading. Although the presence of procid uniquely identifies each
function anyway, if you're going to include the name and argument
information, it might be worth including the argument types as well (as
an array of regtype, perhaps).

> Only functions with oid >= FirstNormalObjectId are accounted.

Sounds reasonable to me; adding profiling to every DirectFunctionCall
invocation is likely to be too expensive anyway.

>From looking quickly at the patch, I don't think the current coding
handles set-returning functions (ExecMakeTableFunctionResult).

-Neil