Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYeBj-0002A3-Fk for pgsql-performance@arkaria.postgresql.org; Thu, 19 Mar 2015 17:24:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YYeBi-0006Hh-Vj for pgsql-performance@arkaria.postgresql.org; Thu, 19 Mar 2015 17:24:55 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YYeBh-0006HT-KL for pgsql-performance@postgresql.org; Thu, 19 Mar 2015 17:24:53 +0000 Received: from iport-acv4-out.ucsd.edu ([132.239.0.7]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_CAMELLIA_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YYeBd-0001CB-Vf for pgsql-performance@postgresql.org; Thu, 19 Mar 2015 17:24:51 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: A2DGBACUBQtV/8wA74RcgmR0WgTEJIF+hXUCgggQAQEBAQEBAXyEEAEFeRACAQgECjEHMhQRAgQOBYgvAQzJSQGEZAEBCAEBAQEBAQEbixeEKgVCB4MXgRYFkEmDboV/gVWMLoYlIoNub4EEQH8BAQE X-IPAS-Result: A2DGBACUBQtV/8wA74RcgmR0WgTEJIF+hXUCgggQAQEBAQEBAXyEEAEFeRACAQgECjEHMhQRAgQOBYgvAQzJSQGEZAEBCAEBAQEBAQEbixeEKgVCB4MXgRYFkEmDboV/gVWMLoYlIoNub4EEQH8BAQE X-IronPort-AV: E=Sophos;i="5.11,430,1422950400"; d="scan'208,217";a="14991615" X-Spam-Status: No X-Spam-Level: Received: from xcore-tpcs2.ucsd.edu (HELO XCORE-TPCS2.AD.UCSD.EDU) ([132.239.0.204]) by iport-acv4-out.ucsd.edu with ESMTP/TLS/AES128-SHA; 19 Mar 2015 10:24:48 -0700 Received: from XMAIL-MBX-BT1.AD.UCSD.EDU ([fe80::b066:a961:2460:32af]) by XCORE-TPCS2.AD.UCSD.EDU ([fe80::95f8:1460:c137:278c%11]) with mapi id 14.03.0224.002; Thu, 19 Mar 2015 10:24:47 -0700 From: "Carson, Leonard" To: Tom Lane CC: "pgsql-performance@postgresql.org" Subject: Re: views much slower in 9.3 than 8.4 Thread-Topic: [PERFORM] views much slower in 9.3 than 8.4 Thread-Index: AQHQYmmZDGU2+JbFMUyHPxiVVEKheA== Date: Thu, 19 Mar 2015 17:24:47 +0000 Message-ID: <66DCD6CD-90FC-4BD8-BF06-E7A4EB3005EC@ucsd.edu> References: <1265DCF8-67C3-4FF3-8737-B2798EE06E25@ucsd.edu> <9C8B9906-D76A-48EE-AED9-516AFD3A4725@ucsd.edu> <5424.1426718490@sss.pgh.pa.us> In-Reply-To: <5424.1426718490@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [137.110.10.122] Content-Type: multipart/alternative; boundary="_000_66DCD6CD90FC4BD8BF06E7A4EB3005ECucsdedu_" MIME-Version: 1.0 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_66DCD6CD90FC4BD8BF06E7A4EB3005ECucsdedu_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 > wrote: "Carson, Leonard" > 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 impo= ssible to recreate the 8.4 environment AFAIK. One of our developers pointe= d out the discrepancy in execution times. I decomposed a slow view and fou= nd 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 --_000_66DCD6CD90FC4BD8BF06E7A4EB3005ECucsdedu_ Content-Type: text/html; charset="us-ascii" Content-ID: Content-Transfer-Encoding: quoted-printable 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 backup= s 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 o= ut 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<= br> the horribly bad join size estimates you're showing.

regards, tom lane

--_000_66DCD6CD90FC4BD8BF06E7A4EB3005ECucsdedu_--