Comment by CuriouslyC
2 years ago
If a function is marked stable it should not impact the query plan at all, since stable functions are essentially in-lined before planning. If logic is unstable a view is probably going to be a better abstraction than a function.
Well I thought so but even with the functions marked IMMUTABLE, which is even more stringent than STABLE the in-lining was not successful, this was apparent in the query plan.
This might be a special case however as the function called another function internally (also IMMUTABLE) which was essentially memoized using an expression index. This is the index that was no-longer hit when inlining failed.
If you think this is bug I think I can create a minimal reproduction.
An immutable function cannot query a table because the table itself isn’t immutable. If your stable/immutable flags don’t match reality, the function can’t be inlined.
Details in sibling but I dug a bit deeper, new version used to_char, turns out that is STABLE and not IMMUTABLE so because the volatility didn't match the whole way down anymore it broke inlining.
I'm guessing switching the function that calls to_char to STABLE will fix the problem.
1 reply →
That does sound like a bug, the planner should be inlining all of that. I would mention it on the postgres mailing list so a committer with more experience in how the planner marshals all that stuff together can weigh in.
I guess I got nerd-sniped. I dug a bit deeper into what was happening and the issue is the new version of the function called to_char() which turns out isn't IMMUTABLE which broke the inlining!