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 3A7ADD1B502 for ; Thu, 18 Sep 2003 22:29:31 +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 61372-09 for ; Thu, 18 Sep 2003 19:29:01 -0300 (ADT) Received: from mail.osdl.org (fw.osdl.org [65.172.181.6]) by svr1.postgresql.org (Postfix) with ESMTP id 77C70D1B4E8 for ; Thu, 18 Sep 2003 19:28:58 -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 h8IMT0U27314 for ; Thu, 18 Sep 2003 15:29:00 -0700 Subject: osdl-dbt3 run results - puzzled by the execution plans From: Jenny Zhang To: pgsql-performance@postgresql.org Content-Type: text/plain Organization: osdl Message-Id: <1063924610.31150.75.camel@ibm-a.pdx.osdl.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Thu, 18 Sep 2003 15:36:50 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/278 X-Sequence-Number: 3644 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