Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id BADECB5DBE8 for ; Thu, 12 May 2011 16:11:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 86577-01 for ; Thu, 12 May 2011 19:10:57 +0000 (UTC) Received: from mx2.hub.org (mx2.hub.org [200.46.204.254]) by mail.postgresql.org (Postfix) with ESMTP id 056C2B5DBE5 for ; Thu, 12 May 2011 16:10:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mx2.hub.org (Postfix) with ESMTP id CA4293EB0864 for ; Thu, 12 May 2011 13:09:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p4CG7sxC018069; Thu, 12 May 2011 12:07:55 -0400 (EDT) To: sthomas@peak6.com cc: Josh Berkus , pgsql-performance@postgresql.org Subject: Re: Postgres refusing to use >1 core In-reply-to: <4DCC0167.8080903@peak6.com> References: <4DC81A3F020000250003D433@gw.wicourts.gov> <4DC81D56020000250003D438@gw.wicourts.gov> <4DC8B893.3060908@postnewspapers.com.au> <4DC9E7C5.9080309@postnewspapers.com.au> <4DCAB3A1.2090507@peak6.com> <201105111953.060147@ms14.lnh.mail.rcn.net> <4DCB0802.9060505@peak6.com> <4DCB346E.8070608@agliodbs.com> <15686.1305211902@sss.pgh.pa.us> <4DCC0167.8080903@peak6.com> Comments: In-reply-to Shaun Thomas message dated "Thu, 12 May 2011 10:48:55 -0500" Date: Thu, 12 May 2011 12:07:54 -0400 Message-ID: <18068.1305216474@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201105/184 X-Sequence-Number: 43693 Shaun Thomas writes: > On 05/12/2011 09:51 AM, Tom Lane wrote: >> It does. I was a bit surprised that Shaun apparently got a plan that >> didn't include a materialize step, because when I test a similar query >> here, I get: > Remember when I said "old version" that prevented us from using CTEs? > We're still on 8.2 (basically, we're waiting for EnterpriseDB 9.0). > It's basically calculating the group aggregation wrong, but is that > enough to trigger it to go nuts? Hmm. As you say, the mistake it's making is a drastic underestimate of the number of groups in the subquery, leading to a bad choice of join method. I find it odd that replacing the subquery with a temp table helps, though, because (unless you stuck in an ANALYZE you didn't mention) it would have no stats at all about the number of groups in the temp table. Maybe the default guess just happens to produce the more desirable plan. regards, tom lane