Search Postgresql Archives

Re: Question about plan difference between 9.3 and 9.3.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Jeff Ross <jross@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx>
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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux