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
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