Re: TPC-R benchmarks - Mailing list pgsql-performance

On Thu, 2003-09-25 at 14:32, Jenny Zhang wrote:
> 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:

  I just want to point out that we are the OSDL are not running
a TPC-X anything.  We have fair use implementations of these
benchmarks but because of differences our performance tests can
not be compared with the TPCC's benchmark results.

> ----------------------------------------------------------------------------------------------------
>  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.
> >
> > *************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Timothy D. Witham - Lab Director - wookie@osdlab.org
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton OR, 97005
(503)-626-2455 x11 (office)    (503)-702-2871     (cell)
(503)-626-2436     (fax)