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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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