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" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: TPC-R benchmarks
Date: 2003-09-25 22:24:59
Message-ID: 1064528699.2082.61.camel@ibm-a.pdx.osdl.net (view raw or flat)
Thread:
Lists: pgsql-performance
The index is created by:
create index i_l_partkey on lineitem (l_partkey);
I do not have any foreign key defined.  Does the spec require foreign
keys?

When you create a foreign key reference, does PG create an index
automatically?

Can you try with the index?

Jenny
On Thu, 2003-09-25 at 14:39, Oleg Lebedev wrote:
> 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:
> 
>    ->  Aggregate  (cost=1517604222.32..1517604222.32 rows=1 width=31)
>          ->  Hash Join  (cost=8518.49..1517604217.39 rows=1969 width=31)
>                Hash Cond: ("outer".l_partkey = "inner".p_partkey)
>                Join Filter: ("outer".l_quantity < (subplan))
>                ->  Seq Scan on lineitem  (cost=0.00..241889.15
> rows=6001215 widt
> h=27)
>                ->  Hash  (cost=8518.00..8518.00 rows=197 width=4)
>                      ->  Seq Scan on part  (cost=0.00..8518.00 rows=197
> width=4)
> 
>                            Filter: ((p_brand = 'Brand#11'::bpchar) AND
> (p_contai
> ner = 'SM PKG'::bpchar))
>                SubPlan
>                  ->  Aggregate  (cost=256892.28..256892.28 rows=1
> width=11)
>                        ->  Seq Scan on lineitem  (cost=0.00..256892.19
> rows=37 w
> idth=11)
>                              Filter: (l_partkey = $0)
> 
> -----Original Message-----
> From: Jenny Zhang [mailto:jenny(at)osdl(dot)org] 
> Sent: Thursday, September 25, 2003 3:33 PM
> To: Oleg Lebedev
> Cc: pgsql-performance(at)postgresql(dot)org;
> osdldbt-general(at)lists(dot)courceforge(dot)net
> Subject: Re: [PERFORM] TPC-R benchmarks
> 
> 
> 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.
> > 
> > *************************************
> 
> *************************************
> 
> 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 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)


In response to

Responses

pgsql-performance by date

Next:From: rantunesDate: 2003-09-25 22:28:40
Subject: Indices arent being used
Previous:From: Oleg LebedevDate: 2003-09-25 21:39:51
Subject: Re: TPC-R benchmarks

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