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@openvistas.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@sss.pgh.pa.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