Re: Immutable function WAY slower than Stable function?

On Tue, Aug 7, 2018 at 9:10 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.)

Finally, I'm wondering if there's any legitimate reason for a immutable
function to call anything stable or volatile, and if not would it make any
sense to emit a warning when the function is created.

> 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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.