Re: views much slower in 9.3 than 8.4

Here are the 3 views and some timing notes:
http://pgsql.privatepaste.com/decae31693#
thanks, lcarson

On Mar 18, 2015, at 3:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:

"Carson, Leonard" <lcarson(at)sdsc(dot)edu<mailto:lcarson(at)sdsc(dot)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