Howdy,
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.
So far I have a "working" patch against 8.2.4 (attached) that places some
instrumentation in the executor (ExecMakeFunctionResult,
ExecMakeFunctionResultNoSets and ExecMakeTableFunctionResult) and send the
stats to the collector (handled similarly to table stats). The results are
visible through pg_stat_user_functions:
martinp=# set stats_function_level = on;
SET
Time: 0.172 ms
martinp=# select count(f3()) from generate_series(1, 1000);
count
-------
1000
(1 row)
Time: 40059.713 ms
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)
Time units are in milliseconds. Only functions with oid >= FirstNormalObjectId
are accounted.
This is of course still very experimental - some work is required to get this
into production shape. I was hoping to get some feedback before I continue
though. Maybe there are some obvious flaws that I'm not seeing, maybe something
needs to be changed to make this more generally useful. Feedback is greatly
appreciated.
Regards,
Martin
