Re: BUG #14162: No statistics for functions used as aggregates

Hello Andrew,

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

Regards,
Daniel Migowski



-----Urspr=FCngliche Nachricht-----
Von: Andrew Gierth [mailto:andrew@tao11.riddles.org.uk]=20
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 aggrega=
tes

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

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

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 abilit=
y to do SET track_functions =3D ... in the definition of a specific functio=
n and have it take effect for that specific call.

(There is some overhead with this, since SET clauses or secdef are implemen=
ted using a shim between the function and its caller, but the overhead shou=
ld 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  Da=
niel> (As they a C functions) or if there are other places where  Daniel> f=
unction tracking isn't implemented although the functions get  Daniel> call=
ed (maybe on index creation, etc.). I need this information  Daniel> becaus=
e I currently try to decide if I have to convert some of  Daniel> these fun=
ctions 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)