From: Mary Edie Meredith <maryedie@osdl.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Oleg Lebedev <oleg.lebedev@waterford.org>
Cc: Jenny Zhang <jenny@osdl.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: TPC-R benchmarks
Date: 26 Sep 2003 09:11:37 -0700
Message-ID: <1064592697.23330.265.camel@ibm-e.pdx.osdl.net> (raw)
In-Reply-To: <8486.1064550534@sss.pgh.pa.us>
References: <993DBE5B4D02194382EC8DF8554A52731D75DC@postoffice.waterford.org>
<8486.1064550534@sss.pgh.pa.us>
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 <oleg.lebedev@waterford.org> 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 <maryedie@osdl.org>
Open Source Development Lab
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: maryedie@osdl.org, tgl@sss.pgh.pa.us, oleg.lebedev@waterford.org, jenny@osdl.org
Subject: Re: TPC-R benchmarks
In-Reply-To: <1064592697.23330.265.camel@ibm-e.pdx.osdl.net>
References: <993DBE5B4D02194382EC8DF8554A52731D75DC@postoffice.waterford.org> <8486.1064550534@sss.pgh.pa.us> <1064592697.23330.265.camel@ibm-e.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