pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jenny Zhang <jenny@osdl.org>
To: Oleg Lebedev <oleg.lebedev@waterford.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: TPC-R benchmarks
Date: Thu, 25 Sep 2003 15:24:59 -0700
Message-ID: <1064528699.2082.61.camel@ibm-a.pdx.osdl.net> (raw)
In-Reply-To: <993DBE5B4D02194382EC8DF8554A52731D75DC@postoffice.waterford.org>
References: <993DBE5B4D02194382EC8DF8554A52731D75DC@postoffice.waterford.org>

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@osdl.org] 
> Sent: Thursday, September 25, 2003 3:33 PM
> To: Oleg Lebedev
> Cc: pgsql-performance@postgresql.org;
> osdldbt-general@lists.courceforge.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@postgresql.org)




view thread (43+ messages)  latest in thread

Message-ID: <1064528699.2082.61.camel@ibm-a.pdx.osdl.net>
Permalink:  ../1064528699.2082.61.camel@ibm-a.pdx.osdl.net/
Also on:    postgresql.org/message-id/1064528699.2082.61.camel@ibm-a.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: jenny@osdl.org, oleg.lebedev@waterford.org
  Subject: Re: TPC-R benchmarks
  In-Reply-To: <1064528699.2082.61.camel@ibm-a.pdx.osdl.net>
  References: <993DBE5B4D02194382EC8DF8554A52731D75DC@postoffice.waterford.org> <1064528699.2082.61.camel@ibm-a.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