Re: BUG #8771: Query execution plan broken after upgrade from 9.1.9

Hi Tom,
I just noticed that the function in the example wasn't explicitly
defined as 'stable'. If I change the function definition to STABLE, the
problem goes away. And the same problem was in our code.
Thank you for pointing me in the right direction, that was very helpful.
Martin.

On 11/01/14 14:32, Tom Lane wrote:
> martin(dot)junek(at)tracmap(dot)co(dot)nz writes:
>> after upgrading from 9.1.9 to 9.1.11 one of our queries started to run
>> terribly slow (went from few miliseconds to hours). The problem is better
>> explained in the following SQL snippet (which is a very simplified version
>> of the problem). If you run it on 9.1.9, all the SELECTs evaluate in few
>> miliseconds, if you run it on 9.1.11, it will take probably hours (I didn't
>> have the patience to wait for it).
> I believe this is a result of this 9.1.11 change:
>
> * Avoid flattening a subquery whose SELECT list contains a volatile function wrapped inside a sub-SELECT (Tom Lane)
>
> This avoids unexpected results due to extra evaluations of the volatile function.
>
> full details of which can be found here:
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=af38d140c71c21eda422fedc838525525d155cac
>
> That's an intentional change that is not going to get undone. If you
> don't like the results, I'd suggest fixing the "slow function" to get it
> marked as stable or immutable as appropriate. (The given example would be
> best marked stable, but I suppose it's just an example and not your real
> problem function.) When it's marked volatile, as this is by default,
> that discourages the planner from rearranging the query in ways that would
> change the number of function executions from what a naive implementation
> would suggest. 9.1.11 is a bit more discouraged than previous releases,
> but it's also less likely to produce surprising results when the function
> is genuinely volatile.
>
> regards, tom lane