Re: TPC-R benchmarks - Mailing list 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@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)