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

1. Basically, I was assuming that "STABLE" was more than just a optimizer "hint".

2. Is it not better to evaluate a STABLE function once, and pass the returned constant to a function, than pass the function itself and inline it? Is not passing a constant more efficient than even an inline query?

Please show me an example where an inline query gets a performance boost.

Thanks!

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, August 11, 2010 4:34 PM
To: Brian Ceccarelli
Cc: Robert Haas; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE

Brian Ceccarelli <bceccarelli(at)net32(dot)com> writes:
> OK. The documentation says "allows the optimizer to optimize . . . ." But then the example guarantees the one-time-only for a index scan condition.

No, the documentation states that *if* an index scan is used, functions
involved in the indexscan's qual condition will be evaluated just once,
rather than once per row. There is no "guarantee" of any sort that such
a plan will be chosen. The point of the STABLE marking is to inform the
optimizer that it is safe to choose an index scan because the function's
results will not change compared to the naive SQL semantics wherein the
WHERE condition is evaluated for each row. Thus, the guarantee actually
runs the other way: you are promising the optimizer that your function
doesn't have side effects or change its results intra-query.

regards, tom lane