Skip site navigation (1) Skip section navigation (2)

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

From: "Lawrence, Ramon" <ramon(dot)lawrence(at)ubc(dot)ca>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: "Bryce Cutt" <pandasuit(at)gmail(dot)com>,"Joshua Tolley" <eggyknap(at)gmail(dot)com>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,<pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets
Date: 2009-02-25 05:38:40
Message-ID: 6EEA43D22289484890D119821101B1DF2C1991@exchange20.mercury.ad.ubc.ca (view raw or flat)
Thread:
Lists: pgsql-hackers
> -----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.

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


--
Ramon Lawrence


Attachment: test.sql
Description: application/octet-stream (12.5 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Alan LiDate: 2009-02-25 07:13:30
Subject: Backend assertion failure on \d
Previous:From: Hiroshi InoueDate: 2009-02-25 05:24:47
Subject: Re: regression test crashes at tsearch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group