Re: optimizing immutable vs. stable function calls?

Karl Czajkowski <karlcz(at)isi(dot)edu> writes:
> The query planner does not seem to
> recognize that it can eliminate redundant calls to a STABLE function.

No, it doesn't.

> In my case, the function call does not take any arguments and is thus
> trivially independent of row data, and appears in a WHERE clause being
> compared to constants. Why wouldn't the optimizer treat this case the
> same as IMMUTABLE?

"The same as IMMUTABLE" would be to reduce the function to a constant at
plan time, which would be the wrong thing. It would be valid to execute
it only once at query start, but there's no built-in mechanism for that.

But you could force it by putting it in a sub-SELECT, that is if you
don't like the performance of

SELECT ... slow_stable_function() ...

try this:

SELECT ... (SELECT slow_stable_function()) ...

That works because it's an uncorrelated sub-query, which gets evaluated
just once per run. But the overhead associated with that mechanism is
high enough that forcing it automatically for every stable function would
be a loser. I'd recommend doing it only where it *really* matters.

regards, tom lane