Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXavN-00058d-HP for pgsql-performance@arkaria.postgresql.org; Mon, 16 Mar 2015 19:43:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXavM-0005cw-Fc for pgsql-performance@arkaria.postgresql.org; Mon, 16 Mar 2015 19:43:40 +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 1YXavL-0005co-6C for pgsql-performance@postgresql.org; Mon, 16 Mar 2015 19:43:39 +0000 Received: from mail-bn1on0626.outbound.protection.outlook.com ([2a01:111:f400:fc10::626] helo=na01-bn1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXavC-0004Ov-Lk for pgsql-performance@postgresql.org; Mon, 16 Mar 2015 19:43:37 +0000 Received: from decina.attlocal.net (2602:306:ce8b:3460:bc26:f347:3a6e:b9f2) by BY1PR11MB0344.namprd11.prod.outlook.com (25.160.206.16) with Microsoft SMTP Server (TLS) id 15.1.112.19; Mon, 16 Mar 2015 19:43:22 +0000 Message-ID: <55073272.20209@BlueTreble.com> Date: Mon, 16 Mar 2015 14:43:46 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Tomas Vondra , , Scott Marlowe , Varadharajan Mukundan CC: Subject: Re: Performance issues References: <84a6fe4be04c3ef0a714851977860ac8@mail.gmail.com> <160b8780d89add7f23c4eb9c3debb99d@mail.gmail.com> <55034A48.4040409@2ndquadrant.com> <849e75d9463938372dd4c516f5c5d89c@mail.gmail.com> <55037CEA.5050906@2ndquadrant.com> In-Reply-To: <55037CEA.5050906@2ndquadrant.com> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:bc26:f347:3a6e:b9f2] X-ClientProxiedBy: DM2PR07CA0039.namprd07.prod.outlook.com (10.141.52.167) To BY1PR11MB0344.namprd11.prod.outlook.com (25.160.206.16) Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none; X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BY1PR11MB0344; X-Forefront-Antispam-Report: BMV:1; SFV:NSPM; SFS:(10009020)(6009001)(24454002)(479174004)(377454003)(47776003)(65956001)(40100003)(92566002)(2950100001)(46102003)(86362001)(50466002)(36756003)(93886004)(77156002)(62966003)(65806001)(122386002)(15975445007)(87976001)(117636001)(54356999)(23746002)(19580395003)(50986999)(42186005)(53416004)(83506001)(3826002)(62816006)(85282002)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BY1PR11MB0344; H:decina.attlocal.net; FPR:; SPF:None; MLV:sfv; LANG:en; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(5002010)(5005006); SRVR:BY1PR11MB0344; BCL:0; PCL:0; RULEID:; SRVR:BY1PR11MB0344; X-Forefront-PRVS: 05177D47DC X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 16 Mar 2015 19:43:22.8539 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR11MB0344 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 On 3/13/15 7:12 PM, Tomas Vondra wrote: > (4) I suspect many of the relations referenced in the views are not > actually needed in the query, i.e. the join is performed but > then it's just discarded because those columns are not used. > Try to simplify the views as much has possible - remove all the > tables that are not really necessary to run the query. If two > queries need different tables, maybe defining two views is > a better approach. A better alternative with multi-purpose views is to use an outer join instead of an inner join. With an outer join if you ultimately don't refer to any of the columns in a particular table Postgres will remove the table from the query completely. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance