Re: views much slower in 9.3 than 8.4 - Mailing list pgsql-performance

From Carson, Leonard
Subject Re: views much slower in 9.3 than 8.4
Date
Msg-id 66DCD6CD-90FC-4BD8-BF06-E7A4EB3005EC@ucsd.edu
Whole thread Raw
In response to Re: Fwd: views much slower in 9.3 than 8.4  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: views much slower in 9.3 than 8.4  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Here are the 3 views and some timing notes:
thanks, lcarson

On Mar 18, 2015, at 3:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Carson, Leonard" <lcarson@sdsc.edu> writes:
There is only one server at this point.  The 8.4 machine was upgraded to 9.3 about a year ago and we have no 8.4 backups so it's difficult if not impossible to recreate the 8.4 environment AFAIK.  One of our developers pointed out the discrepancy in execution times.  I decomposed a slow view and found out that it consists of a view calling a view calling a view (3 deep).  This is the analyze explain plan of the innermost view:

http://explain.depesz.com/s/IMg

You're probably going to need to show us the actual view definitions.

I'm suspicious that the underlying cause might have to do with recent
versions being warier about optimizing sub-selects containing volatile
functions than 8.4 was.  However, that theory doesn't seem to explain
the horribly bad join size estimates you're showing.

regards, tom lane

pgsql-performance by date:

Previous
From: Jake Magner
Date:
Subject: Re: Merge Join chooses very slow index scan
Next
From: Sergey Shchukin
Date:
Subject: Re: [GENERAL] Re: [pgadmin-support] Issue with a hanging apply process on the replica db after vacuum works on primary