On Wed, Jan 18, 2017 at 4:23 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johns...@gmail.com> writes: > > I'm feeling a bit dense here but even after having read a number of > these > > kinds of interchanges I still can't get it to stick. I think part of the > > problem is this sentence from the docs: > > https://www.postgresql.org/docs/current/static/xfunc-volatility.html > > > (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" should probably be dispelled. Thanks! David J.
