BUG #14162: No statistics for functions used as aggregates

On Sat, May 28, 2016 at 10:52 PM, <dmigowski@ikoffice.de> wrote:

I created some user defined aggregate with a user defined plpgsql SFUNC.

The calls to plpgsql functions used in these aggregates are not counted by
the statistics collector when the aggregate is used, but calling them
directly will work.

Are you sure that track_functions is enabled? If it disabled by
default. In order to track the number of calls of user-defined
functions it needs to be set to "pl" or "all".
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Yes, I am sure. To measure my function I had to recreate it with a different name, and create a plpgsql wrapper function with the original signature that calls it. Now I was able to get my results and the function showed up in the stats. The wrapper didn't!

-----Ursprüngliche Nachricht-----
Von: Michael Paquier [mailto:michael.paquier@gmail.com]
Gesendet: Montag, 30. Mai 2016 05:53
An: Daniel Migowski <dmigowski@ikoffice.de>
Cc: PostgreSQL mailing lists <pgsql-bugs@postgresql.org>
Betreff: Re: [BUGS] BUG #14162: No statistics for functions used as aggregates

On Sat, May 28, 2016 at 10:52 PM, <dmigowski@ikoffice.de> wrote:

I created some user defined aggregate with a user defined plpgsql SFUNC.

The calls to plpgsql functions used in these aggregates are not
counted by the statistics collector when the aggregate is used, but
calling them directly will work.

Are you sure that track_functions is enabled? If it disabled by default. In order to track the number of calls of user-defined functions it needs to be set to "pl" or "all".
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, May 30, 2016 at 4:56 PM, Daniel Migowski <dmigowski@ikoffice.de> wrote:

Yes, I am sure. To measure my function I had to recreate it with a different name, and create a plpgsql wrapper function with the original signature that calls it. Now I was able to get my results and the function showed up in the stats. The wrapper didn't!

(please do not top-post it breaks the logic of the thread).
Ah I see I got confused, nodeAgg.c does not track statistics of the
transition or final functions when an aggregate is invoked, and has
never done it. There is no call to pgstat_init_function_usage() when
FunctionCallInvoke() is called so you cannot track statistics
regarding that. Maybe that would be a interesting feature, but
controlled by a different GUC than track_functions.
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Ok, then no statistics for me :). If this is intentional, a small line or warning in the docs would be helpful.

I also wonder if in and out functions of datatypes are tracked (As they a C functions) or if there are other places where function tracking isn't implemented although the functions get called (maybe on index creation, etc.). I need this information because I currently try to decide if I have to convert some of these functions to native c functions.

-----Ursprüngliche Nachricht-----
Von: Michael Paquier [mailto:michael.paquier@gmail.com]
Gesendet: Dienstag, 31. Mai 2016 06:57
An: Daniel Migowski <dmigowski@ikoffice.de>
Cc: PostgreSQL mailing lists <pgsql-bugs@postgresql.org>
Betreff: Re: [BUGS] BUG #14162: No statistics for functions used as aggregates

On Mon, May 30, 2016 at 4:56 PM, Daniel Migowski <dmigowski@ikoffice.de> wrote:

Yes, I am sure. To measure my function I had to recreate it with a different name, and create a plpgsql wrapper function with the original signature that calls it. Now I was able to get my results and the function showed up in the stats. The wrapper didn't!

(please do not top-post it breaks the logic of the thread).
Ah I see I got confused, nodeAgg.c does not track statistics of the transition or final functions when an aggregate is invoked, and has never done it. There is no call to pgstat_init_function_usage() when
FunctionCallInvoke() is called so you cannot track statistics regarding that. Maybe that would be a interesting feature, but controlled by a different GUC than track_functions.
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Daniel" == Daniel Migowski <dmigowski@ikoffice.de> writes:

Daniel> Ok, then no statistics for me :). If this is intentional, a
Daniel> small line or warning in the docs would be helpful.

There's a workaround, though it's a somewhat bizarre one:

If you apply any SET clause to the definition of a function, then calls
to it will be tracked (if either track_functions is globally enabled or
if the SET clause enables it) regardless of the context the function is
called in. This is also true for security definer functions.

This doesn't seem to be entirely intentional, it is fallout from the
ability to do SET track_functions = ... in the definition of a specific
function and have it take effect for that specific call.

(There is some overhead with this, since SET clauses or secdef are
implemented using a shim between the function and its caller, but the
overhead should be much less than a plpgsql wrapper. Also, this will
disable inlining of the function if it is LANGUAGE SQL.)

Daniel> I also wonder if in and out functions of datatypes are tracked
Daniel> (As they a C functions) or if there are other places where
Daniel> function tracking isn't implemented although the functions get
Daniel> called (maybe on index creation, etc.). I need this information
Daniel> because I currently try to decide if I have to convert some of
Daniel> these functions to native c functions.

As far as I can tell, only the following cases are tracked:

- functions called as triggers or event triggers
- functions called from the expression evaluator
- functions called as FROM-clause entries in queries
- any function which has a SET clause or is SECURITY DEFINER

Not tracked are any other cases, which include:

- the function-call fastpath from clients
- window functions and aggregate functions
- functions used for sort/group/distinct/hash comparisons
- index support functions
- input and output functions
- estimation functions
- any case where a function calls another function without going
via SQL

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hello Andrew,

thanks for the nice info about the trick with SET, and your elaborations regarding which functions are tracked. This is definitely useful for me.

Regards,
Daniel Migowski

-----Ursprüngliche Nachricht-----
Von: Andrew Gierth [mailto:andrew@tao11.riddles.org.uk]
Gesendet: Mittwoch, 1. Juni 2016 04:16
An: Daniel Migowski <dmigowski@ikoffice.de>
Cc: Michael Paquier <michael.paquier@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@postgresql.org>
Betreff: Re: [BUGS] BUG #14162: No statistics for functions used as aggregates

"Daniel" == Daniel Migowski <dmigowski@ikoffice.de> writes:

Daniel> Ok, then no statistics for me :). If this is intentional, a Daniel> small line or warning in the docs would be helpful.

There's a workaround, though it's a somewhat bizarre one:

If you apply any SET clause to the definition of a function, then calls to it will be tracked (if either track_functions is globally enabled or if the SET clause enables it) regardless of the context the function is called in. This is also true for security definer functions.

This doesn't seem to be entirely intentional, it is fallout from the ability to do SET track_functions = ... in the definition of a specific function and have it take effect for that specific call.

(There is some overhead with this, since SET clauses or secdef are implemented using a shim between the function and its caller, but the overhead should be much less than a plpgsql wrapper. Also, this will disable inlining of the function if it is LANGUAGE SQL.)

Daniel> I also wonder if in and out functions of datatypes are tracked Daniel> (As they a C functions) or if there are other places where Daniel> function tracking isn't implemented although the functions get Daniel> called (maybe on index creation, etc.). I need this information Daniel> because I currently try to decide if I have to convert some of Daniel> these functions to native c functions.

As far as I can tell, only the following cases are tracked:

- functions called as triggers or event triggers
- functions called from the expression evaluator
- functions called as FROM-clause entries in queries
- any function which has a SET clause or is SECURITY DEFINER

Not tracked are any other cases, which include:

- the function-call fastpath from clients
- window functions and aggregate functions
- functions used for sort/group/distinct/hash comparisons
- index support functions
- input and output functions
- estimation functions
- any case where a function calls another function without going
via SQL

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs