Skip site navigation (1) Skip section navigation (2)

Re: TPC-R benchmarks

From: Jenny Zhang <jenny(at)osdl(dot)org>
To: Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org,osdldbt-general(at)lists(dot)courceforge(dot)net
Subject: Re: TPC-R benchmarks
Date: 2003-09-25 21:32:35
Message-ID: 1064525555.2082.39.camel@ibm-a.pdx.osdl.net (view raw or flat)
Thread:
Lists: pgsql-performance
I am running TPC-H with scale factor of 1 on RedHat7.2 with the kernel
2.5.74.  Q17 can always finish in about 7 seconds on my system.  The
execution plan is:
----------------------------------------------------------------------------------------------------
 Aggregate  (cost=780402.43..780402.43 rows=1 width=48)
   ->  Nested Loop  (cost=0.00..780397.50 rows=1973 width=48)
         Join Filter: ("inner".l_quantity < (subplan))
         ->  Seq Scan on part  (cost=0.00..8548.00 rows=197 width=12)
               Filter: ((p_brand = 'Brand#31'::bpchar) AND (p_container
= 'LG CASE'::bpchar))
         ->  Index Scan using i_l_partkey on lineitem 
(cost=0.00..124.32 rows=30 width=36)
               Index Cond: ("outer".p_partkey = lineitem.l_partkey)
         SubPlan
           ->  Aggregate  (cost=124.40..124.40 rows=1 width=11)
                 ->  Index Scan using i_l_partkey on lineitem 
(cost=0.00..124.32 rows=30 width=11)
                       Index Cond: (l_partkey = $0)
(11 rows)

Hope this helps,
Jenny
On Thu, 2003-09-25 at 12:40, Oleg Lebedev wrote:
> I am running TPC-R benchmarks with a scale factor of 1, which correspond
> to approximately 1 GB database size on PostgreSQL 7.3.4 installed on
> CygWin on Windows XP. I dedicated 128 MB of shared memory to my postrges
> installation.
> Most of the queries were able to complete in a matter of minutes, but
> query 17 was taking hours and hours. The query is show below. Is there
> any way to optimize it ?
>  
> select
>  sum(l_extendedprice) / 7.0 as avg_yearly
> from
>  lineitem,
>  part
> where
>  p_partkey = l_partkey
>  and p_brand = 'Brand#11'
>  and p_container = 'SM PKG'
>  and l_quantity < (
>   select
>    0.2 * avg(l_quantity)
>   from
>    lineitem
>   where
>    l_partkey = p_partkey
>  );
>  
> Thanks.
>  
> Oleg
> 
> *************************************
> 
> 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. 
> 
> *************************************


In response to

Responses

pgsql-performance by date

Next:From: Oleg LebedevDate: 2003-09-25 21:39:51
Subject: Re: TPC-R benchmarks
Previous:From: Oleg LebedevDate: 2003-09-25 19:40:12
Subject: TPC-R benchmarks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group