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 B5E0AD1B533 for ; Fri, 26 Sep 2003 16:12:08 +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 61418-02 for ; Fri, 26 Sep 2003 13:11:58 -0300 (ADT) Received: from mail.osdl.org (fw.osdl.org [65.172.181.6]) by svr1.postgresql.org (Postfix) with ESMTP id C1F4DD1B4F5 for ; Fri, 26 Sep 2003 13:11:51 -0300 (ADT) Received: from ibm-e.pdx.osdl.net (ibm-e.pdx.osdl.net [172.20.1.71]) by mail.osdl.org (8.11.6/8.11.6) with ESMTP id h8QGBb128363; Fri, 26 Sep 2003 09:11:37 -0700 Subject: Re: TPC-R benchmarks From: Mary Edie Meredith To: Tom Lane Cc: Oleg Lebedev , Jenny Zhang , pgsql-performance In-Reply-To: <8486.1064550534@sss.pgh.pa.us> References: <993DBE5B4D02194382EC8DF8554A52731D75DC@postoffice.waterford.org> <8486.1064550534@sss.pgh.pa.us> Content-Type: text/plain Organization: Open Source Development Lab Message-Id: <1064592697.23330.265.camel@ibm-e.pdx.osdl.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 26 Sep 2003 09:11:37 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/332 X-Sequence-Number: 3698 The TPC-H/R rules allow only minor changes to the SQL that are necessary due to SQL implementation differences. They do not allow changes made to improve performance. It is their way to test optimizer's ability to recognize an inefficient SQL statement and do the rewrite. The rule makes sense for the TPC-H, which is supposed to represent ad-Hoc query. One might argue that for TPC-R, which is suppose to represent "Reporting" with pre-knowledge of the query, that re-write should be allowed. However, that is currently not the case. Since the RDBMS's represented on the TPC council are competing with TPC-H, their optimizers already do the re-write, so (IMHO) there is no motivation to relax the rules for the TPC-R. On Thu, 2003-09-25 at 21:28, Tom Lane wrote: > Oleg Lebedev writes: > > Seems like in your case postgres uses an i_l_partkey index on lineitem > > table. I have a foreign key constraint defined between the lineitem and > > part table, but didn't create an special indexes. Here is my query plan: > > The planner is obviously unhappy with this plan (note the large cost > numbers), but it can't find a way to do better. An index on > lineitem.l_partkey would help, I think. > > The whole query seems like it's written in a very inefficient fashion; > couldn't the estimation of '0.2 * avg(l_quantity)' be amortized across > multiple join rows? But I dunno whether the TPC rules allow for > significant manual rewriting of the given query. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Mary Edie Meredith Open Source Development Lab