Re: [PERFORM] optimizing immutable vs. stable function calls? - Mailing list pgsql-performance

From Tom Lane
Subject Re: [PERFORM] optimizing immutable vs. stable function calls?
Date
Msg-id 14964.1484780049@sss.pgh.pa.us
Whole thread Raw
Responses Re: [PERFORM] optimizing immutable vs. stable function calls?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: [PERFORM] optimizing immutable vs. stable function calls?  (Karl Czajkowski <karlcz@isi.edu>)
List pgsql-performance
Karl Czajkowski <karlcz@isi.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


pgsql-performance by date:

Previous
From: Karl Czajkowski
Date:
Subject: [PERFORM] optimizing immutable vs. stable function calls?
Next
From: Karl Czajkowski
Date:
Subject: Re: [PERFORM] optimizing immutable vs. stable function calls?