Skip site navigation (1) Skip section navigation (2)

TPCH 100GB - need some help

From: Eduardo Almeida <edalmeida(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: TPCH 100GB - need some help
Date: 2004-05-14 18:00:37
Message-ID: 20040514180037.8730.qmail@web60608.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi folks,

I need some help in a TPCH 100GB benchmark.

I described our settings in:
http://archives.postgresql.org/pgsql-performance/2004-04/msg00377.php

Some queries are taking to long to finish (4, 8, 9,
10, 19,20 and 22) and I need some help to increase the
system performance.
Here I put the query #19, the explain and the "top"
for it. 
This query is running since yesterday 10 AM.

Query text is:

select
        sum(l_extendedprice* (1 - l_discount)) as
revenue
from
        lineitem,
        part
where
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#32'
                and p_container in ('SM CASE', 'SM
BOX', 'SM PACK', 'SM PKG')
                and l_quantity >= 2 and l_quantity <=
2 + 10
                and p_size between 1 and 5
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN
PERSON'
        )
        or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#42'
                and p_container in ('MED BAG', 'MED
BOX', 'MED PKG', 'MED PACK')
                and l_quantity >= 11 and l_quantity <=
11 + 10
                and p_size between 1 and 10
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN
PERSON'
        )
     or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#54'
                and p_container in ('LG CASE', 'LG
BOX', 'LG PACK', 'LG PKG')
                and l_quantity >= 27 and l_quantity <=
27 + 10
                and p_size between 1 and 15
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN
PERSON'
        );



Tasks:  57 total,   2 running,  55 sleeping,   0
stopped,   0 zombie
Cpu(s):  16.5% user,   1.8% system,   0.0% nice, 
59.2% idle,  22.5% IO-wait
Mem:   4036184k total,  4025008k used,    11176k free,
    4868k buffers
Swap:  4088500k total,    13204k used,  4075296k free,
 3770208k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM   
TIME+  COMMAND
28118 postgres  25   0  372m 354m 335m R 99.4  9.0  
1724:45 postmaster


 Aggregate 
(cost=6825900228313539.00..6825900228313539.00 rows=1
width=22)
   ->  Nested Loop 
(cost=887411.00..6825900228313538.00 rows=325
width=22)
         ->  Seq Scan on lineitem 
(cost=0.00..21797716.88 rows=600037888 width=79)
         ->  Materialize  (cost=887411.00..1263193.00
rows=20000000 width=36)
               ->  Seq Scan on part 
(cost=0.00..711629.00 rows=20000000 width=36)



	
		
__________________________________
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/

Responses

pgsql-performance by date

Next:From: Jaime CasanovaDate: 2004-05-14 21:08:19
Subject: numeric data types
Previous:From: Fabio PanizzuttiDate: 2004-05-14 10:30:16
Subject: R: R: R: Query plan on identical tables differs . Why ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group