Comment by jpgvm
2 years ago
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.
Makes sense. You mentioned index so I thought maybe you were were querying a table.
Would be nice if postgresql could tell you when the flags don’t match. I think anytime you deal with timestamps you can have problems since the expression may depend on the session’s time zone.
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!