pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Jim Nasby <jnasby@enova.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Problems with hash join over nested loop
Date: Tue, 29 Oct 2013 15:08:28 -0500
Message-ID: <527015BC.3020405@enova.com> (raw)
In-Reply-To: <5575.1383070823@sss.pgh.pa.us>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
view thread (13+ messages) latest in thread
Message-ID: <527015BC.3020405@enova.com> Permalink: ../527015BC.3020405@enova.com/ Also on: postgresql.org/message-id/527015BC.3020405@enova.com ·
reply
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: jnasby@enova.com, tgl@sss.pgh.pa.us, mmoncure@gmail.com Subject: Re: Problems with hash join over nested loop In-Reply-To: <527015BC.3020405@enova.com> * 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