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
