pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mary Edie Meredith <maryedie@osdl.org>
To: Oleg Lebedev <oleg.lebedev@waterford.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jenny Zhang <jenny@osdl.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: TPC-R benchmarks
Date: 29 Sep 2003 09:04:09 -0700
Message-ID: <1064851449.23330.2942.camel@ibm-e.pdx.osdl.net> (raw)
In-Reply-To: <993DBE5B4D02194382EC8DF8554A52731E782A@postoffice.waterford.org>
References: <993DBE5B4D02194382EC8DF8554A52731E782A@postoffice.waterford.org>

On Mon, 2003-09-29 at 07:35, Oleg Lebedev wrote:
> I left my TPC-R query #17 working over the weekend and it took 3988 mins
> ~ 10 hours to complete. And this is considering that I am using a TPC-R
> database created with a scale factor of 1, which corresponds to ~1 GB of
> data. I am running RedHat 8.0 on a dual 1 GHz processor, 512 MB RAM.

Was this run with or without the l_partkey index that Jenny suggested? 

> 
> Here is an excerpt from my postgresql.conf file (the rest of the
> settings are commented out):
> 
> #
> #	Shared Memory Size
> #
> shared_buffers = 16384		# 2*max_connections, min 16, typically
> 8KB each
> 
> #
> #	Non-shared Memory Sizes
> #
> sort_mem = 32768
> 
> #
> #	Optimizer Parameters
> #
> effective_cache_size = 32000	# typically 8KB each
> 
> Any suggestions on how to optimize these settings?
> 
> I agree with Jenny that declaring additional indexes on the TPC-R tables
> may alter the validity of the benchmarks. Are there any official TPC
> benchmarks submitted by PostgreSQL? 

Actually, for the TPC-R you _are allowed to declare additional indexes. 
With TPC-H you are restricted to a specific set listed in the spec (an
index on l_partkey is allowed for both).

What you cannot do for either TPC-R or TPC-H is rewrite the SQL of the
query for the purposes of making the query run faster.

Sorry if I was unclear.

Valid TPC-R benchmark results are on the TPC web site:
http://www.tpc.org/tpcr/default.asp  

I do not see one for PostgreSQL.


Regards,

Mary 

-- 
Mary Edie Meredith <maryedie@osdl.org>
Open Source Development Lab

> 
> Thanks.
> 
> Oleg
> 
> -----Original Message-----
> From: Mary Edie Meredith [mailto:maryedie@osdl.org] 
> Sent: Friday, September 26, 2003 10:12 AM
> To: Tom Lane
> Cc: Oleg Lebedev; Jenny Zhang; pgsql-performance
> Subject: Re: [PERFORM] TPC-R benchmarks
> 
> 
> 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




view thread (43+ messages)  latest in thread

Message-ID: <1064851449.23330.2942.camel@ibm-e.pdx.osdl.net>
Permalink:  ../1064851449.23330.2942.camel@ibm-e.pdx.osdl.net/
Also on:    postgresql.org/message-id/1064851449.23330.2942.camel@ibm-e.pdx.osdl.net

 · 

reply

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, oleg.lebedev@waterford.org, tgl@sss.pgh.pa.us, jenny@osdl.org
  Subject: Re: TPC-R benchmarks
  In-Reply-To: <1064851449.23330.2942.camel@ibm-e.pdx.osdl.net>
  References: <993DBE5B4D02194382EC8DF8554A52731E782A@postoffice.waterford.org> <1064851449.23330.2942.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