Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50687=pgsql-performance-ar@postgresql.org>)
 id 1VbC2o-0003oN-Lq
 for pgsql-performance@arkaria.postgresql.org; Tue, 29 Oct 2013 16:21:26 +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+M50687=pgsql-performance-ar@postgresql.org>)
 id 1VbC2n-0008AV-U5
 for pgsql-performance@arkaria.postgresql.org; Tue, 29 Oct 2013 16:21:25 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1VbC2m-0008AM-JN
 for pgsql-performance@postgresql.org; Tue, 29 Oct 2013 16:21:24 +0000
Received: from exprod5og107.obsmtp.com ([64.18.0.184])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1VbC2h-0004ly-Tr
 for pgsql-performance@postgresql.org; Tue, 29 Oct 2013 16:21:23 +0000
Received: from mail-ie0-f180.google.com ([209.85.223.180]) (using TLSv1) by
 exprod5ob107.postini.com ([64.18.4.12]) with SMTP
 ID DSNKUm/gfrz4l3R3RxMezdh+p/SrS6BOAc0w@postini.com;
 Tue, 29 Oct 2013 09:21:19 PDT
Received: by mail-ie0-f180.google.com with SMTP id e14so140955iej.25
 for <pgsql-performance@postgresql.org>; Tue, 29 Oct 2013 09:21:18 -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=9jXzClq9QPU1juLRHNtyJtm4xuOkgy6Fa6PN8nrGNnc=;
 b=UZRRXAargkzY3wTheruEmK8oxeCVR2E1y0gazcFpcHSFz8DBxEhKBO2b/udWl6GXiJ
 1X3p2T2VtWisMPtqxQyw7PKFE3OueiT0soJ31DzbTrzWDKudhUWeoq3HLoWepvrcf5SQ
 UXqadEaWV5+N8E/AJo71d9SohCMp/R94fEBns=
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=9jXzClq9QPU1juLRHNtyJtm4xuOkgy6Fa6PN8nrGNnc=;
 b=cU72u47gvK1szs7gaf/OdFZk4l+ryiu999pRV/AvYyGXitP91Ku4kHnf1HbhSp3bzo
 2dqxV+Z4q9fR2VJIj7lStG9tYpjUPv8367pC6fuFAfg9pc2OI0evuD/HAFEu26koQT8l
 oGufx4TATRWNVi3S218j+1heDpN+gr9mRznB83zYKdmxzs+N0NabwnahNNWTgVm9Fjq1
 ctCEpnARKK4VzINUG+hIqlP0i7Vzm1bC2CqXXdK1EVBRkHHbdD2/nuMf13FfZzoojhjh
 WMgvdb4qx5sQ7TuSHzupiYLWMesHOkrPro0UwmrBXiYMXyKeK0ch4iSB6dtXPGIeJisw
 CNlw==
X-Gm-Message-State: 
 ALoCoQn1xJP6wtEDC4SwAFxis07CFdi/M0xt+7E2hmvxcZf+Hp1QoCHyNAAAuKs3Qj3emEOZBxGDqXuTpZuE2RXWB7ncCy3Fz/lK8oJm91Yi46F4meKe/gDvOt4I4643SJZMjdTQ22eQImqoYVpEk2Hos0QnZ4+GwjE0fGlfco52Km0EQdmaWUtF5cjpskTdt+FEQbOvm4yR
X-Received: by 10.42.72.198 with SMTP id p6mr183289icj.71.1383063678167;
 Tue, 29 Oct 2013 09:21:18 -0700 (PDT)
X-Received: by 10.42.72.198 with SMTP id p6mr183281icj.71.1383063678049;
 Tue, 29 Oct 2013 09:21:18 -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 cy15sm3117385igc.1.2013.10.29.09.21.16
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Tue, 29 Oct 2013 09:21:17 -0700 (PDT)
Message-ID: <526FE086.4020903@enova.com>
Date: Tue, 29 Oct 2013 11:21:26 -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: Merlin Moncure <mmoncure@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
CC: "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>
In-Reply-To: 
 <CAHyXU0ydScKt=-5G4yBpHWAh9eO6eV60P6uVCnui+YS9EMY5vw@mail.gmail.com>
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 9:10 AM, Merlin Moncure wrote:
> On Mon, Oct 28, 2013 at 6:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Jim Nasby <jnasby@enova.com> writes:
>>> I've been working on trying to normalize a table that's got a bunch of text fields. Normalizing the first 4 has been a non-issue. But when I try and normalize 2 additional fields a bunch of query plans go belly-up.
>>
>> Try increasing join_collapse_limit/from_collapse_limit.  I'm a bit
>> confused by your description but I think maybe you've got more than 8
>> relations in the subqueries.
>
> Hm -- wondering out loud if there would be any value in terms of
> decorating explain output when that limit was hit and if it's
> practical to do so...

I think the community would *love* any method of noting potential performance problems. Hitting the GEQO limit fits in there as well. We could eventually warn about other things as well, like going just over work_mem or seqscanning a big table for a small number of rows.

I'm also wondering if it's time to raise those limits. I constructed a somewhat contrived test query in our schema to test this. This is a legitimate join path for our schema... I can't see why someone would use the *full* path, but smaller sections are definitely in use. It's basically all joins, with one simple filter on top of that.

I'd rather not share the actual query or plan, but:

grep -i scan temp.txt |wc -l
28

All tests done via EXPLAIN ... in psql with \timing turned on. I ignored obvious outliers... margin of error is ~5% from what I saw:

Default config:			21ms
geqo = off:			19ms
geqo off, from_collapse = 99:	19ms
from_collapse_limit = 99:	21ms
join_collapse_limit = 99:	171ms
both = 99:			176ms
geqo off, join_collapse = 99	1.2s
both + geqo = off:		1.2s

Obviously there's cases where 1.2 seconds of planning time will kill you... but if you're that time sensitive and using 28 tables I think it's reasonable to expect people to do some hand tuning! :)

Conversely, where you are likely to get to that sheer number of tables is when you're doing something that's going to take a non-trivial amount of time to execute. In this particular case, if I limit the query to a single row (via blah_id = 2, not via limit), it takes ~2ms to execute when cached with full optimization (interestingly, planning time was at about 926ms at that point).

Now that looks horrible... 926ms to plan a query that takes 2ms to return. But I'm not even going to bother with the 20ms plan, because it's going to take minutes if not HOURS to run (it's just full scanning everything it can find).
-- 
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