Re: Invalid optimization of VOLATILE function in WHERE clause?

On Wed, Sep 19, 2012 at 2:39 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> It still seems like awfully weird behavior.
>>
>> Why? The WHERE condition relates only to the output of the _stats
>> subquery, so why shouldn't it be evaluated there, rather than
>> after the join?
>
> In another thread, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It's easier to understand why this is if you realize that SQL has
>> a very clear model of a "pipeline" of query execution.
>> Conceptually, what happens is:
>>
>> 1. Form the cartesian product of the tables listed in FROM (ie,
>> all combinations of rows).
>>
>> 2. Apply the WHERE condition to each row from 1, and drop rows
>> that don't pass it.
>
> People expect that the results will be consistent with this model,
> even if the implementation is optimized "under the covers". I think
> correct semantics should trump performance here.

Hm, I bet it's possible (although probably not easy) to deduce
volatility from the function body...maybe through the validator. If
you could do that (perhaps warning in cases where you can't), then the
performance regression-inducing-argument (which I agree with) becomes
greatly ameliorated.

merlin