Re: TPC-R benchmarks

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.

*************************************