When are SQL functions inlined & how can I tell?
I've been having some serious (> 100x) slowness in some of my code
which uses nice maintainable little SQL functions versus when I do
the same code as a big messy expression. I got rid of STRICT, I've
tried STABLE and even IMMUTABLE and I can't seem to get the speed
difference to go away, so here are some questions:
(1) What are the current criteria for when an SQL function called
within another SQL function will be inlined?
(2) Is there some easy way to tell without massive logging whether
a particular function has been inlined? I've tried doing various
EXPLAIN ... SELECT foo(...);
kinds of things where foo() calls bar() and I don't see that I can
tell anything about the inline status of either function.
Thanks,
_Greg
J. Greg Davidson
"J. Greg Davidson" <jgd@well.com> writes:
(1) What are the current criteria for when an SQL function called
within another SQL function will be inlined?
It depends on a number of things, not least which PG version you're
talking about. When in doubt, consult the source code (see
inline_function or inline_set_returning_function in
src/backend/optimizer/util/clauses.c)
(2) Is there some easy way to tell without massive logging whether
a particular function has been inlined? I've tried doing various
EXPLAIN ... SELECT foo(...);
kinds of things where foo() calls bar() and I don't see that I can
tell anything about the inline status of either function.
Try EXPLAIN VERBOSE. You should see either the function or its
expansion.
regards, tom lane