> (Stable): "This category allows the optimizer to optimize multiple calls > of the function to a single call"
> If PostgreSQL cannot execute it only once at query start then all this talk > about optimization seems misleading. At worse there should be an sentence > explaining when the optimizations noted in that paragraph cannot occur - > and probably examples of both as well since its not clear when it can occur.
If you want an exact definition of when things will happen or not happen, start reading the source code. I'm loath to document small optimizer details since they change all the time.
That would not be a productive exercise for me, or most people who just want
some idea of what to expect in terms of behavior when they write and use a
Stable function (Immutable and Volatile seem fairly easy to reason about).
Is there anything fatally wrong with the following comprehension?
"""
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 take an expression of the form
(indexed_column = stable_function(...)) and evaluate stable_function(...)
once at the beginning of the query and use the result to scan
the index. (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). ?Note that should an index scan not be
chosen for the plan the function will be invoked once-per-row?
Expressions of the forms (constant = stable_function()),
and (SELECT stable_function() FROM generate_series(1,5)) are not presently
optimized to a single per-query evaluation. To obtain the equivalent you
can invoke the function in a sub-query or CTE and reference the result
wherever it is needed.
"""
It probably isn't perfect but if the average user isn't going to benefit from
anything besides "index_column = function()" with an index plan then the
false hope that is being held due to the use of "allows + in particular"
