Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWDT1-0003uA-VO for pgsql-performance@arkaria.postgresql.org; Mon, 22 Sep 2014 23:56:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWDT1-00058w-Es for pgsql-performance@arkaria.postgresql.org; Mon, 22 Sep 2014 23:56:27 +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 1XWDT0-00058k-7O for pgsql-performance@postgresql.org; Mon, 22 Sep 2014 23:56:26 +0000 Received: from 01.zmailcloud.com ([192.198.85.104] helo=mx-out-1.zmailcloud.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWDSw-0003PW-Tz for pgsql-performance@postgresql.org; Mon, 22 Sep 2014 23:56:24 +0000 Received: from smtp.01.com (smtp.01.com [10.10.0.43]) by mx-out-1.zmailcloud.com (Postfix) with ESMTP id 794DE564447; Mon, 22 Sep 2014 18:56:22 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 5EF10A0584; Mon, 22 Sep 2014 18:56:22 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id beENB_XwW3Fd; Mon, 22 Sep 2014 18:56:22 -0500 (CDT) Received: from smtp.01.com (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 3844AA0605; Mon, 22 Sep 2014 18:56:22 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 1B535A05FF; Mon, 22 Sep 2014 18:56:22 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id VPuWEwblJGff; Mon, 22 Sep 2014 18:56:21 -0500 (CDT) Received: from [172.47.23.104] (70-36-143-82.dsl.dynamic.sonic.net [70.36.143.82]) by smtp-out-1.01.com (Postfix) with ESMTPSA id 0A039A0584; Mon, 22 Sep 2014 18:56:14 -0500 (CDT) Message-ID: <5420B71C.6010208@agliodbs.com> Date: Mon, 22 Sep 2014 16:56:12 -0700 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.5.0 MIME-Version: 1.0 To: Merlin Moncure CC: Greg Stark , postgres performance list Subject: Re: Yet another abort-early plan disaster on 9.3 References: <541A2335.3060100@agliodbs.com> <541C7891.7000902@agliodbs.com> <541DC87F.3030800@agliodbs.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 09/22/2014 06:55 AM, Merlin Moncure wrote: > Has any progress been made on the performance farm? The problem with > suggestions like this (which seem pretty reasonable to me) is that > we've got no way of quantifying the downside. Yeah, that's certainly an issue. The problem is that we'd need a benchmark which actually created complex query plans. I believe that Mark Wong is working on TPCH-based benchmarks, so maybe we'll get that. > I think this is one > example of a class of plans that are high risk. Another one off the > top of my head is nestloop joins based on assumed selectivity of > multiple stacked quals. Yeah, that's another good example. > About 90% of the time, my reflective > workaround to these types of problems is to 'disable_nestloop' which > works around 90% of the time and the result are solved with monkeying > around with 'OFFSET 0' etc. In the past, a GUC controlling planner > risk has been much discussed -- maybe it's still worth considering? We've hashed that out a bit, but frankly I think it's much more profitable to pursue fixing the actual problem than providing a workaround like "risk", such as: a) fixing n_distinct estimation b) estimating stacked quals using better math (i.e. not assuming total randomness) c) developing some kind of correlation stats Otherwise we would be just providing users with another knob there's no rational way to set. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance