I wrote:
>> This seems to indicate some estimation problems in cost_hashjoin; the
>> estimated cost for the hashjoin is evidently a lot higher than it should
>> be.
The answer is that estimate_hash_bucketsize() is producing a rather
silly result in this situation, viz. a bucketsize "fraction" that's well
above 1.0. I've applied the following band-aid patch to CVS tip, which
perhaps you might like to use locally. But probably the long-range
answer is to rethink what that routine is doing --- its adjustment for
skewed data distributions is perhaps not such a great idea.
regards, tom lane
*** src/backend/optimizer/path/costsize.c.orig Fri Dec 13 19:17:55 2002
--- src/backend/optimizer/path/costsize.c Thu Dec 26 18:34:02 2002
***************
*** 1164,1169 ****
--- 1164,1179 ----
if (avgfreq > 0.0 && mcvfreq > avgfreq)
estfract *= mcvfreq / avgfreq;
+ /*
+ * Clamp bucketsize to sane range (the above adjustment could easily
+ * produce an out-of-range result). We set the lower bound a little
+ * above zero, since zero isn't a very sane result.
+ */
+ if (estfract < 1.0e-6)
+ estfract = 1.0e-6;
+ else if (estfract > 1.0)
+ estfract = 1.0;
+
ReleaseSysCache(tuple);
return (Selectivity) estfract;