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)
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