pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jenny Zhang <jenny@osdl.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: perf-pgsql <pgsql-performance@postgresql.org>
Subject: Re: osdl-dbt3 run results - puzzled by the execution
Date: Fri, 19 Sep 2003 14:35:41 -0700
Message-ID: <1064007341.442.45.camel@ibm-a.pdx.osdl.net> (raw)
In-Reply-To: <8224.1063941637@sss.pgh.pa.us>
References: <1063924610.31150.75.camel@ibm-a.pdx.osdl.net>
	<8224.1063941637@sss.pgh.pa.us>

On Thu, 2003-09-18 at 20:20, Tom Lane wrote:
> Jenny Zhang <jenny@osdl.org> writes:
> > ...  It seems to me that small 
> > effective_cache_size favors the choice of nested loop joins (NLJ) 
> > while the big effective_cache_size is in favor of merge joins (MJ).  
> 
> No, I wouldn't think that, because a nestloop plan will involve repeated
> fetches of the same tuples whereas a merge join doesn't (at least not
> when it sorts its inner input, as this plan does).  Larger cache
> improves the odds of a repeated fetch not having to do I/O.  In practice
> a larger cache area would also have some effects on access costs for the
> sort's temp file, but I don't think the planner's cost model for sorting
> takes that into account.
I think there is some misunderstanding here.  What I meant to say is:
From the plans we got, the optimizer favors the choice of nested loop
joins (NLJ) while the big effective_cache_size is in favor of merge
joins (MJ).  Which we think is not appropriate.  We verified that
sort_mem has no impact on the plans.  Though it would be nice to take
that into account.
> 
> As Matt Clark points out nearby, the real question is whether these
> planner estimates have anything to do with reality.  EXPLAIN ANALYZE
> results would be far more interesting than plain EXPLAIN.
> 
> > However, within the same run set consist of 6 runs, we see 2-3% 
> > standard deviation for the run metrics associated with the multiple
> > stream part of the test (as opposed to the single stream part).
> 
> <python> Och, laddie, we useta *dream* of 2-3% variation </python>
> 
BTW, I am a she :-)
> > We would like to reduce the variation to be less than 1% so that a 
> > 2% change between two different kernels would be significant. 
> 
> I think this is a pipe dream.  Variation in where the data gets laid
> down on your disk drive would alone create more than that kind of delta.
> I'm frankly amazed you could get repeatability within 2-3%.
> 
Greg is right.  The repeatability is due to the aggregate results for a
whole test run.  As for individual query, the power test(single stream)
is very consistent, and the throughput test(multiple streams), any given
query execution time varies up to 15% if no swapping.  If we set
sort_mem too high and swapping occurs, the variation is bigger.

Jenny




view thread (12+ messages)  latest in thread

Message-ID: <1064007341.442.45.camel@ibm-a.pdx.osdl.net>
Permalink:  ../1064007341.442.45.camel@ibm-a.pdx.osdl.net/
Also on:    postgresql.org/message-id/1064007341.442.45.camel@ibm-a.pdx.osdl.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jenny@osdl.org, tgl@sss.pgh.pa.us
  Subject: Re: osdl-dbt3 run results - puzzled by the execution
  In-Reply-To: <1064007341.442.45.camel@ibm-a.pdx.osdl.net>
  References: <1063924610.31150.75.camel@ibm-a.pdx.osdl.net> <8224.1063941637@sss.pgh.pa.us> <1064007341.442.45.camel@ibm-a.pdx.osdl.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox