Re: Immutable function WAY slower than Stable function?

Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Mon, Aug 6, 2018 at 7:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The reason that EXPLAIN VERBOSE
>> is helpful here is that you can see whether the function got inlined
>> or not: do you see a call to the function, or a representation of
>> its body?

> There is the difference in the two output lines, which I guess suggests
> that the stable one got inlined and the immutable one didn't?

> *   Output: client_id, si_imm(client_id)

Yeah, exactly: here we have the original call to si_imm ...

> *   Output: client_id, staff_inspector_stable(client_id, target_date())

... but here, that's been replaced by si_imm's body.  So we've
successfully eliminated one layer of SQL-function call overhead.

The reason this happens in one case and not the other is that one of
the arcane rules for function inlining is that it's not allowed to make
the expression look more volatile than it was before.  So if si_imm()
is marked immutable, inlining it is disallowed if it would insert any
stable or volatile functions.  I can give you a disquisition on why
that is if you like, but it's not really relevant right now, since
clearly none of these functions should have been marked immutable.

Still, though, we've only eliminated the SQL-function overhead for one
out of three SQL functions that are getting called at each row, so you'd
think the inlining could not save more than 1/3rd of the runtime at the
absolute most.  How is it making more than an order-of-magnitude
difference?

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 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.

            regards, tom lane