Re: Immutable function WAY slower than Stable function?

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.

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.

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.