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