pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@peak6.com
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres refusing to use >1 core
Date: Thu, 12 May 2011 12:07:54 -0400
Message-ID: <18068.1305216474@sss.pgh.pa.us> (raw)
In-Reply-To: <4DCC0167.8080903@peak6.com>
References: <BANLkTimohiUa6rOYdxYTwqmvSUg2CRYMHg@mail.gmail.com>
	<4DC81A3F020000250003D433@gw.wicourts.gov>
	<BANLkTimNTLqQgQeyu=FPOn-5x6F4XnVBJA@mail.gmail.com>
	<4DC81D56020000250003D438@gw.wicourts.gov>
	<BANLkTi=FH4rX-7OtS9ozjuyXQTNTiRqCmg@mail.gmail.com>
	<BANLkTinwkj7syvKkYzU_O7DD14EMzk2Zmg@mail.gmail.com>
	<BANLkTi=BvGMvsBNq8DUt=_MPJXZjcC0TWg@mail.gmail.com>
	<4DC8B893.3060908@postnewspapers.com.au>
	<BANLkTimi+2nviLvWU4zKGtG688+Z84DF6w@mail.gmail.com>
	<4DC9E7C5.9080309@postnewspapers.com.au>
	<BANLkTi=PxROPQFDt1a6N68e_+5_AojagmA@mail.gmail.com>
	<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>

Shaun Thomas <sthomas@peak6.com> 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



view thread (46+ messages)  latest in thread

Message-ID: <18068.1305216474@sss.pgh.pa.us>
Permalink:  ../18068.1305216474@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18068.1305216474@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, sthomas@peak6.com, josh@agliodbs.com
  Subject: Re: Postgres refusing to use >1 core
  In-Reply-To: <18068.1305216474@sss.pgh.pa.us>

* 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