Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50691=pgsql-performance-ar@postgresql.org>)
 id 1VbFaX-00058u-86
 for pgsql-performance@arkaria.postgresql.org; Tue, 29 Oct 2013 20:08:29 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50691=pgsql-performance-ar@postgresql.org>)
 id 1VbFaW-0004dW-HT
 for pgsql-performance@arkaria.postgresql.org; Tue, 29 Oct 2013 20:08:28 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1VbFaV-0004dM-DY
 for pgsql-performance@postgresql.org; Tue, 29 Oct 2013 20:08:27 +0000
Received: from exprod5og107.obsmtp.com ([64.18.0.184])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1VbFaR-0000zq-0b
 for pgsql-performance@postgresql.org; Tue, 29 Oct 2013 20:08:26 +0000
Received: from mail-ie0-f175.google.com ([209.85.223.175]) (using TLSv1) by
 exprod5ob107.postini.com ([64.18.4.12]) with SMTP
 ID DSNKUnAVteN588ws080xIoV7WfxpVWWYKCgz@postini.com;
 Tue, 29 Oct 2013 13:08:22 PDT
Received: by mail-ie0-f175.google.com with SMTP id aq17so657086iec.34
 for <pgsql-performance@postgresql.org>; Tue, 29 Oct 2013 13:08:20 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enova.com; s=google;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=mSL07QF3fbaYdr1RH/q4PCdSqnz9i4MqmWP2Rqjd3oo=;
 b=Xd6D+MXf4QTjeQPfSAC+bmECDprorh5CdkxcNLLC9KR3k00xITqC2bc1AM4mXjtnPV
 J0vhmf4b0aR20G10d1mpGwre95JVCP65V6qAxjUNclobcSaB2b0qYvlva6TbX/aGB7uO
 VbolwYsDGdUY5n3QQ3EPsyycKjdLClBI9jWMY=
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to
 :cc:subject:references:in-reply-to:content-type
 :content-transfer-encoding;
 bh=mSL07QF3fbaYdr1RH/q4PCdSqnz9i4MqmWP2Rqjd3oo=;
 b=k4DzzL9qRq0VqsZi4jj62x8sO0lA6yQxoXlTTD9O8CY7m+ZK3HK6Suo2n9FnrN3sei
 +Ya3SuudCH9RSDv0Zxtk2N6gU32JBZXWbrQFqHJM+FtdfS7XWqu6xzP+eiB6H7buvS3O
 nZzpqLI4lIVRmIOsT308QuYCfP5mecnl4g1VW3Mbjn8aDE8VKbVb5badGuNzIEDWN5/U
 D8qyOT2VjWiX001bP97I0r4f1u7Vw3Qw1oITthJUBMyZ74zW+7rx2BO+TJRm1mPn5DiQ
 POVRR6sG1xRznAIEsB+bKtoijnEYiIbwae7D2c01Ooq0tQj1y6UZ4iy2Q6c4sZJyA4c9
 cJ6A==
X-Gm-Message-State: 
 ALoCoQmdlP1s8qlh/mnm/Bh17PhUNnWsASqqsprotFU38sqVbYxCqh5PhX5uBMsyAcbg2AqWX9BXkxuV8uGpd9LtFYRK2a3usy+BSiX1/jgQaYomYPeEqyOoJkku5Tp0PBBsWHln9DrHHJgn0Pk4qhjUYcbJWA9zTGQFILgqxjDMux04CRIhBmRH948OjJMQ8onQ2ERqkOgM
X-Received: by 10.42.86.73 with SMTP id t9mr14234icl.81.1383077300913;
 Tue, 29 Oct 2013 13:08:20 -0700 (PDT)
X-Received: by 10.42.86.73 with SMTP id t9mr14227icl.81.1383077300810;
 Tue, 29 Oct 2013 13:08:20 -0700 (PDT)
Received: from decina.local (rrcs-67-79-71-90.sw.biz.rr.com. [67.79.71.90])
 by mx.google.com with ESMTPSA id i3sm3722476igh.0.2013.10.29.13.08.19
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Tue, 29 Oct 2013 13:08:20 -0700 (PDT)
Message-ID: <527015BC.3020405@enova.com>
Date: Tue, 29 Oct 2013 15:08:28 -0500
From: Jim Nasby <jnasby@enova.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8;
 rv:24.0) Gecko/20100101 Thunderbird/24.0.1
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Merlin Moncure <mmoncure@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Problems with hash join over nested loop
References: <526EDDF7.3090102@enova.com> <13775.1383002035@sss.pgh.pa.us>
 <CAHyXU0ydScKt=-5G4yBpHWAh9eO6eV60P6uVCnui+YS9EMY5vw@mail.gmail.com>
 <526FE086.4020903@enova.com> <3692.1383065114@sss.pgh.pa.us>
 <526FE7D0.9000605@enova.com> <5575.1383070823@sss.pgh.pa.us>
In-Reply-To: <5575.1383070823@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -4.3 (----)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 10/29/13 1:20 PM, Tom Lane wrote:
> Jim Nasby <jnasby@enova.com> writes:
>> On 10/29/13 11:45 AM, Tom Lane wrote:
>>> Jim Nasby <jnasby@enova.com> writes:
>>>> I'm also wondering if it's time to raise those limits.
>
>>> Yeah, possibly.  The current default values were set on machines much
>>> smaller/slower than most current hardware.
>>>
>>> I think also that the collapse limits were invented mainly to keep people
>>> out of GEQO's clutches, but we've made some significant fixes in GEQO
>>> since then.  Maybe the real answer is to make the default collapse limits
>>> much higher, and lower geqo_threshold to whatever we think the threshold
>>> of pain is for applying the regular planner.
>
>> In my test case geqo does seem to do a good job. I'll see if I can get some data on how number of relations affects planning time... I don't get much of a warm fuzzy about lowering geqo...
>
> Yeah, it's probably not that simple.  A trawl through the archives
> reminded me that we've discussed this quite a bit in the past already.
> The collapse limits are important for the regular planner not only to
> limit runtime but also to limit planner memory consumption; moreover,
> GEQO doesn't behave all that well either with very large join problems.
> These facts killed a proposal back in 2009 to remove the collapse limits
> altogether.  There was also some discussion in 2011, see thread here:
> http://www.postgresql.org/message-id/BANLkTin4ncKMg+bEixi1WB1RJPrZ5dVRgQ@mail.gmail.com
> but the general feeling seemed to be that we needed more planner
> infrastructure work first.  In particular it seems like the best way
> forward might require limiting subproblem size using something more
> sophisticated than just "number of relations".

Yeah, I saw one mention of 1GB... that's a bit disconcerting.

Is there a way to measure memory consumption during planning, short of something like strace? (I've got no dev tools available on our servers.)
-- 
Jim Nasby, Lead Data Architect   (512) 569-9461


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance