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 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? Thanks. Oleg -----Original Message----- 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 > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > 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 recipient only. 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