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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Neil Conway wrote:
>
> (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).
>

This is true. I was being a bit selfish here - in our environment we don't
use overloading that much - schema, name and nargs are usually sufficient.
It is also convinient to be able to do "select * from pg..." and have the
output not to wrap around.

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

Hmm, should be handled. The number of tuples returned is not counted though,
perhaps this is another thing to add ...

Regards,
Martin

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly