X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id CE3E1D1B559 for ; Fri, 19 Sep 2003 21:28:06 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 63043-03 for ; Fri, 19 Sep 2003 18:27:58 -0300 (ADT) Received: from mail.osdl.org (fw.osdl.org [65.172.181.6]) by svr1.postgresql.org (Postfix) with ESMTP id 6271AD1B4E3 for ; Fri, 19 Sep 2003 18:27:55 -0300 (ADT) Received: from ibm-a.pdx.osdl.net (ibm-a.pdx.osdl.net [172.20.1.50]) by mail.osdl.org (8.11.6/8.11.6) with ESMTP id h8JLRoU18866; Fri, 19 Sep 2003 14:27:50 -0700 Subject: Re: osdl-dbt3 run results - puzzled by the execution From: Jenny Zhang To: Tom Lane Cc: perf-pgsql 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> Content-Type: text/plain Organization: osdl Message-Id: <1064007341.442.45.camel@ibm-a.pdx.osdl.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Fri, 19 Sep 2003 14:35:41 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/287 X-Sequence-Number: 3653 On Thu, 2003-09-18 at 20:20, Tom Lane wrote: > Jenny Zhang 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). > > Och, laddie, we useta *dream* of 2-3% variation > 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