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

On Wed, Feb 25, 2009 at 12:38 AM, Lawrence, Ramon <ramon(dot)lawrence(at)ubc(dot)ca> wrote:
>> -----Original Message-----
>> From: Robert Haas
>> Sadly, there seem to be a number of cases in the Z7 database where the
>> optimization makes things significantly worse (specifically, queries
>> 2, 3, and 7, but especially query 3).  Have you investigated what is
>> going on there?  I had thought that we had sufficient safeguards in
>> place to prevent this optimization from kicking in in cases where it
>> doesn't help, but it seems not.  There will certainly be real-world
>> databases that are more like Z7 than Z1.
>
> I agree that there should be no noticeable performance difference when
> the optimization is not used (single batch case or no skew).  I think
> the patch achieves this.  The optimization is not used in those cases,
> but we will review to see if it is the code that by-passes the
> optimization that is causing a difference.

Yeah we need to understand what's going on there.

> The query #3 timing difference is primarily due to a flaw in the
> experimental setup.  For some reason, query #3 got executed before #4
> with the optimization on, and executed after #4 with the optimization
> off.  This skewed the results for all runs (due to buffering issues),
> but is especially noticeable for Z7.  Note how query #4 is always faster
> for the optimization on version even though the optimization is not
> actually used for those queries (because they were one batch).  I expect
> that if you run query #3 on Z7 in isolation then the results should be
> basically identical.
>
> I have attached the SQL script that Joshua sent me.  The raw data I have
> posted at: http://people.ok.ubc.ca/rlawrenc/test.output

I don't think we're really doing this the right way. EXPLAIN ANALYZE
has a measurable effect on the results, and we probably ought to stop
the database and drop the VM caches after each query. Are the Z1-Z7
datasets on line someplace? I might be able to rig up a script here.

...Robert