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.
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
# 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?
From: Mary Edie Meredith [mailto:[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that
> message can get through to the mailing list cleanly
Mary Edie Meredith <[EMAIL PROTECTED]>
Open Source Development Lab
This e-mail may contain privileged or confidential material intended for the named
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