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

On Wed, Aug 11, 2010 at 11:50 AM, Brian Ceccarelli
<bceccarelli@net32.com> wrote:
> OK. =A0 The documentation says "allows the optimizer to optimize . . . ."=
 =A0 =A0But then the example guarantees the one-time-only for a index scan =
condition.
>
> From the documentation: =A0 =A08.4.4 Chapter 32 and 8.2.17 Chapter 33.
>
> =A0 .A STABLE function cannot modify the database and is guaranteed to re=
turn the same results given the same arguments for all rows within a =A0sin=
gle statement. This category allows the optimizer to optimize multiple call=
s of the function to a single call. In particular, it is safe to use an exp=
ression containing such a function in an index scan condition. (Since an in=
dex 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 <=3D 8.2 certainly fit the description. =
=A0 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.

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