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/
