Re: Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets

> From: Tom Lane
> Heikki's got a point here: the planner is aware that hashjoin doesn't
> like skewed distributions, and it assigns extra cost accordingly if it
> can determine that the join key is skewed.  (See the "bucketsize"
stuff
> in cost_hashjoin.)  If this patch is accepted we'll want to tweak that
> code.

Those modifications would make the optimizer more likely to select hash
join, even with skewed distributions.  For the TPC-H data set that we
are using the optimizer always picks hash join over merge join (single
or multi-batch).  Since the current patch does not change the cost
function, there is no change in the planning cost.  It may or may not be
useful to modify the cost function depending on the effect on planning
cost.

> Still, that has little to do with the current gating issue, which is
> whether we've convinced ourselves that the patch doesn't cause a
> performance decrease for cases in which it's unable to help.

Although we have not seen an overhead when the optimization is
by-passed, we are looking at some small code changes that would
guarantee that no extra statements are executed for the single batch
case.  Currently, an if optimization_on check is performed on each probe
tuple which, although minor, should be able to be avoided.  

The patch's author, Bryce Cutt, is defending his Master's thesis Friday
morning (on this work), so we will provide some updated code right after
that.  Since these code changes are small, they should not affect people
trying to test the performance of the current patch.

--
Ramon Lawrence