From: Carson, Leonard <lcarson@sdsc.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: views much slower in 9.3 than 8.4
Date: Thu, 19 Mar 2015 17:24:47 +0000
Message-ID: <66DCD6CD-90FC-4BD8-BF06-E7A4EB3005EC@ucsd.edu> (raw)
In-Reply-To: <5424.1426718490@sss.pgh.pa.us>
References: <1265DCF8-67C3-4FF3-8737-B2798EE06E25@ucsd.edu>
<9C8B9906-D76A-48EE-AED9-516AFD3A4725@ucsd.edu>
<5424.1426718490@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
"Carson, Leonard" <lcarson@sdsc.edu<mailto: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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lcarson@sdsc.edu, tgl@sss.pgh.pa.us
Subject: Re: views much slower in 9.3 than 8.4
In-Reply-To: <66DCD6CD-90FC-4BD8-BF06-E7A4EB3005EC@ucsd.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox