Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

On Wed, Aug 11, 2010 at 11:50 AM, Brian Ceccarelli
<bceccarelli(at)net32(dot)com> wrote:
> OK.   The documentation says "allows the optimizer to optimize . . . ."    But then the example guarantees the one-time-only for a index scan condition.
>
> From the documentation:    8.4.4 Chapter 32 and 8.2.17 Chapter 33.
>
>   .A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a  single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.)
>
> The behavior of the optimizers <= 8.2 certainly fit the description.   The 8.4 behavior is vastly different.

Reading between the lines, I think I sense that this has got you
pretty frustrated, so in defense of the new behavior, let me just
mention that, in general, inlining SQL queries results in a HUGE
performance benefit. It's sort of unfortunate that it doesn't work
out that way for you in this case, but I don't think it's a bad idea
in general.

*thinks*

In theory, the optimization Brian wants is possible here, right? I
mean, you could replace the functional call with a Param, and pull the
Param out and make it an InitPlan. Seems like that would generally be
a win, if you figure to loop more than once and the execution cost of
the function is not too tiny.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company