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

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