pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jenny Zhang <jenny@osdl.org>
To: pgsql-performance@postgresql.org
Subject: osdl-dbt3 run results - puzzled by the execution plans
Date: Thu, 18 Sep 2003 15:36:50 -0700
Message-ID: <1063924610.31150.75.camel@ibm-a.pdx.osdl.net> (raw)

Our hardware/software configuration:
kernel: 2.5.74
distro: RH7.2
pgsql:  7.3.3
CPUS:   8
MHz:    700.217
model:  Pentium III (Cascades)
memory: 8298888 kB
shmmax: 3705032704

We did several sets of runs(repeating runs with the same database
parameters) and have the following observation:

1. With everything else the same, we did two run sets with
small effective_cache_size (default=1000) and large (655360 i.e. 5GB
or 60% of the system memory 8GB).  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).  
We thought the large effective_cache_size should lead us to better 
plans. But we found the opposite. 

Three plans out of 22 are different.  Two of those plans are worse 
in execution time by 2 times and 8 times.   For example, one plan, 
that included  NLJ ran in 4 seconds but the other, switching to an 
MJ, ran in  32 seconds.  Please refer to the link at the end of 
this mail for the query and plans.  Did we miss something, or 
improvements are needed for the optimizer?

2. Thanks to all the response we got from this mailing list, we 
decided to use SETSEED(0) default_statistics_target=1000 to reduce 
the variation.   We get now the exact the same execution plans 
and costs with repeated runs and that reduced the variation a lot.
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).

We would like to reduce the variation to be less than 1% so that a 
2% change between two different kernels would be significant. 
Is there anything else we can do?

query: http://developer.osdl.org/~jenny/11.sql
plan with small effective_cache_size: 
http://developer.osdl.org/~jenny/small_effective_cache_size_plan
plan with large effective_cache_size: 
http://developer.osdl.org/~jenny/large_effective_cache_size_plan

Thanks,
Jenny




view thread (12+ messages)  latest in thread

Message-ID: <1063924610.31150.75.camel@ibm-a.pdx.osdl.net>
Permalink:  ../1063924610.31150.75.camel@ibm-a.pdx.osdl.net/
Also on:    postgresql.org/message-id/1063924610.31150.75.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
  Subject: Re: osdl-dbt3 run results - puzzled by the execution plans
  In-Reply-To: <1063924610.31150.75.camel@ibm-a.pdx.osdl.net>
  References: <1063924610.31150.75.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