Re: Question about plan difference between 9.3 and 9.3.2

On 1/17/14, 11:02 AM, Tom Lane wrote:
> Jeff Ross <jross(at)openvistas(dot)net> writes:
>> I had to move our production database to a new server with virtually
>> identical hardware. At the same time I went to 9.3.2 from 9.3.
>> Queries on the old server (nirvana) run many magnitudes faster than on
>> the new server (dukkha).
>> The two are configured the same except for the IP address to listen on.
>> Here's an example of the difference between the old and new.
> It looks like the newer server is not flattening the view before
> optimizing. Given that there's not that much distance between 9.3.0
> and 9.3.2, I'd guess that the culprit must be this change:
>
> Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Branch: master [b97ee66cc] 2013-11-08 11:36:57 -0500
> Branch: REL9_3_STABLE Release: REL9_3_2 [9548bee2b] 2013-11-08 11:37:00 -0500
> Branch: REL9_2_STABLE Release: REL9_2_6 [f7171c7e2] 2013-11-08 11:37:04 -0500
> Branch: REL9_1_STABLE Release: REL9_1_11 [af38d140c] 2013-11-08 11:37:08 -0500
> Branch: REL9_0_STABLE Release: REL9_0_15 [987f05e16] 2013-11-08 11:37:12 -0500
> Branch: REL8_4_STABLE Release: REL8_4_19 [90b07dd7b] 2013-11-08 11:37:17 -0500
>
> Make contain_volatile_functions/contain_mutable_functions look into SubLinks.
>
> This change prevents us from doing inappropriate subquery flattening in
> cases such as dangerous functions hidden inside a sub-SELECT in the
> targetlist of another sub-SELECT. That could result in unexpected behavior
> due to multiple evaluations of a volatile function, as in a recent
> complaint from Etienne Dube. It's been questionable from the very
> beginning whether these functions should look into subqueries (as noted in
> their comments), and this case seems to provide proof that they should.
>
> Because the new code only descends into SubLinks, not SubPlans or
> InitPlans, the change only affects the planner's behavior during
> prepjointree processing and not later on --- for example, you can still get
> it to use a volatile function in an indexqual if you wrap the function in
> (SELECT ...). That's a historical behavior, for sure, but it's reasonable
> given that the executor's evaluation rules for subplans don't depend on
> whether there are volatile functions inside them. In any case, we need to
> constrain the behavioral change as narrowly as we can to make this
> reasonable to back-patch.
>
> You didn't show us the view, but is there a volatile function hidden
> inside a sub-select in its SELECT list? If so, can you safely change
> that function to stable or immutable marking?
>
> regards, tom lane
>
>
Yes I did have a volatile function in that view and yes, changing it to
immutable fixed the problem!

Thanks, Tom!

Jeff