Message-ID: <507C1AAF.20008@dhs-club.com>
Date: Mon, 15 Oct 2012 10:16:15 -0400
From: Bill MacArthur <webmaster@dhs-club.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-admin@postgresql.org
Subject: Re: query from 9.0 fails to return on 9.2
References: <50786A75.5000601@dhs-club.com> <21576.1350073671@sss.pgh.pa.us>
In-Reply-To: <21576.1350073671@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 10/12/2012 4:27 PM, Tom Lane wrote:
> Bill MacArthur <webmaster@dhs-club.com> writes:
>> Here is the top line of the *really long* explain as run on a 9.0 server (it takes only a couple of seconds to return): HashAggregate  (cost=1148.97..1149.36 rows=1 width=205)
>> However, the same query run on the same machine after upgrade to 9.2.1 never returns. The query was sitting there for 10 hours unbeknownst to me.
>
> I'm wondering what values of join_collapse_limit & from_collapse_limit
> you're using on each machine.
>
> If it's not an oops-forgot-to-copy-the-configuration problem, you're
> going to need to put together a self-contained test case.
>
> 			regards, tom lane
>

Thanks for the feedback, Tom.
Those two settings remained at the defaults of 8 for both in both versions.
I did a quick google on the latter setting and found an ongoing discussion from back in 2011 involving increasing the default, but your concern was that planner memory usage would escalate considerably by doing so. Given that information, I am inclined to ruminate on the query and boil it down to something simpler (less joins). Unless you think there is value to the greater good in determining what the underlying issue is, I won't bother creating a test case... at least not until I spend some time trying to rework the thing.