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 46465D1B547 for ; Tue, 7 Oct 2003 18:35:44 +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 69264-09 for ; Tue, 7 Oct 2003 15:34:55 -0300 (ADT) Received: from mail.osdl.org (fw.osdl.org [65.172.181.6]) by svr1.postgresql.org (Postfix) with ESMTP id CA8CAD1B4EF for ; Tue, 7 Oct 2003 15:34:51 -0300 (ADT) Received: from localhost (build.pdx.osdl.net [172.20.1.2]) by mail.osdl.org (8.11.6/8.11.6) with ESMTP id h97IYX103123; Tue, 7 Oct 2003 11:34:33 -0700 Subject: Re: TPC-R benchmarks From: "Timothy D. Witham" To: Jenny Zhang Cc: Oleg Lebedev , pgsql-performance@postgresql.org, osdldbt-general@lists.courceforge.net In-Reply-To: <1064525555.2082.39.camel@ibm-a.pdx.osdl.net> References: <993DBE5B4D02194382EC8DF8554A52731D75CD@postoffice.waterford.org> <1064525555.2082.39.camel@ibm-a.pdx.osdl.net> Content-Type: text/plain Organization: Open Source Development Lab, Inc. Message-Id: <1065551673.1376.88.camel@wookie-t23> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Tue, 07 Oct 2003 11:34:33 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/194 X-Sequence-Number: 3942 On Thu, 2003-09-25 at 14:32, Jenny Zhang wrote: > I am running TPC-H with scale factor of 1 on RedHat7.2 with the kernel > 2.5.74. Q17 can always finish in about 7 seconds on my system. The > execution plan is: I just want to point out that we are the OSDL are not running a TPC-X anything. We have fair use implementations of these benchmarks but because of differences our performance tests can not be compared with the TPCC's benchmark results. > ---------------------------------------------------------------------------------------------------- > Aggregate (cost=780402.43..780402.43 rows=1 width=48) > -> Nested Loop (cost=0.00..780397.50 rows=1973 width=48) > Join Filter: ("inner".l_quantity < (subplan)) > -> Seq Scan on part (cost=0.00..8548.00 rows=197 width=12) > Filter: ((p_brand = 'Brand#31'::bpchar) AND (p_container > = 'LG CASE'::bpchar)) > -> Index Scan using i_l_partkey on lineitem > (cost=0.00..124.32 rows=30 width=36) > Index Cond: ("outer".p_partkey = lineitem.l_partkey) > SubPlan > -> Aggregate (cost=124.40..124.40 rows=1 width=11) > -> Index Scan using i_l_partkey on lineitem > (cost=0.00..124.32 rows=30 width=11) > Index Cond: (l_partkey = $0) > (11 rows) > > Hope this helps, > Jenny > On Thu, 2003-09-25 at 12:40, Oleg Lebedev wrote: > > I am running TPC-R benchmarks with a scale factor of 1, which correspond > > to approximately 1 GB database size on PostgreSQL 7.3.4 installed on > > CygWin on Windows XP. I dedicated 128 MB of shared memory to my postrges > > installation. > > Most of the queries were able to complete in a matter of minutes, but > > query 17 was taking hours and hours. The query is show below. Is there > > any way to optimize it ? > > > > select > > sum(l_extendedprice) / 7.0 as avg_yearly > > from > > lineitem, > > part > > where > > p_partkey = l_partkey > > and p_brand = 'Brand#11' > > and p_container = 'SM PKG' > > and l_quantity < ( > > select > > 0.2 * avg(l_quantity) > > from > > lineitem > > where > > l_partkey = p_partkey > > ); > > > > Thanks. > > > > Oleg > > > > ************************************* > > > > This e-mail may contain privileged or confidential material intended for the named recipient only. > > If you are not the named recipient, delete this message and all attachments. > > Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. > > We reserve the right to monitor e-mail sent through our network. > > > > ************************************* > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Timothy D. Witham - Lab Director - wookie@osdlab.org Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton OR, 97005 (503)-626-2455 x11 (office) (503)-702-2871 (cell) (503)-626-2436 (fax)