On Tue, Aug 7, 2018 at 9:10 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
I was confused about that too, so I set up a simple test case similar
to Ken's and poked into it a bit, and what I found out is that nested
SQL functions are just about completely broken performance-wise,
unless one or the other gets inlined :-(. The reason is that the SQL
function mechanism is designed to cache the results of parsing and
planning the function body ... but only for the duration of the
calling query. So if you issue a direct SQL command "SELECT myf(x)
FROM big_table", and myf() isn't inline-able, then myf's body gets
parsed/planned once and then re-used at each row of the table. That's
not too awful. But each execution of myf's body counts as a separate
"calling query" for this purpose, so if it's calling some other SQL
function that's also not inline-able, then we re-parse-and-plan that
other function for each row of big_table. *That* is where the time
is going.
Glad you were able to dig into this and figure it out!
As a couple of follow-ups:
After I submitted the (corrected) explain for my attempt to inline-by-hand the SQL from the function directly into the query, that the inlined version took about twice as long as the call with the function. (2.5 vs 1.3 seconds.) That doesn't really make sense to me. I guess I could use that auto_explain stuff to see what's going on, but if there's an obvious answer...
I've wondered in the past about Immutable functions, and particularly the scope & lifetime of "forever" in "guaranteed to return the same results given the same arguments forever." I assume that's "for all users and all sessions," but either in theory or in practice is there a limit to how long a stale value might persist? And, if you were to drop and recreate a function with the same name & parameters, would it start fresh at that point? And is there a way to flush any caching? (It's surely best to just declare Stable, but I'm wondering about cases that might have _very_ infrequently-changed values.)
I've had a to-do item to rewrite and improve the SQL function cache
mechanism for a long time, but I hadn't thought it was high priority.
Maybe it should be.
That sounds great. My earlier misguided statement notwithstanding, we've actually tried to write our functions in SQL wherever possible, assuming that the performance will be better than with a procedural language.
In the meantime, I think you could dodge the issue by converting either
level of function into plpgsql. Or just be careful about inline-ability
of convenience wrappers.
Definitely yes on the second. And to be clear, my actual problem was dodged before starting this thread. I was aiming more towards understanding the behavior, which seemed either amiss or in need of some documentation. And trying to be Postgres-helpful where I can in a non-developer way. Kinda like "see something / say something" for software users. :)
Thanks again.
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
