Ken Tanzer <ken.tanzer@xxxxxxxxx> writes: > On Mon, Aug 6, 2018 at 7:42 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
