"Paragon Corporation" <lr@pcorp.us> writes:
> Guys I tried increasing cost with the ST_Reclass function to 2000 and it didn't help. The function is called for
everysingle output. The only thing that helps is setting this to VOLATILE as Tom suggests. I guess we can do that
assome sort of fix for these kinds of functions though I'm not sure if that would cause other issues.
I think that would be a very bad idea; it would foreclose optimizations
that you *do* want. Much better, if you are relying on single-evaluation
behavior for a non-volatile function, is to use one of the documented
optimization fences: either a CTE, or an OFFSET 0 in a sub-select.
TBH, this particular example does not fill me with concern, because
(a) it's obviously artificial, and (b) you'd really never notice if the
function got evaluated 3 times not once, if you hadn't put in that NOTICE.
I grant that there may be cases where you're worried about avoiding
multiple evaluations *per row* over some large number of rows, but to
discuss that type of problem we'd have to see what your coding habits are
like for such cases. An immutable function being fed constants is not
going to create that type of problem.
regards, tom lane