Shaun Thomas <sthomas@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
