TPCH 100GB - need some help
От | Eduardo Almeida |
---|---|
Тема | TPCH 100GB - need some help |
Дата | |
Msg-id | 20040514180037.8730.qmail@web60608.mail.yahoo.com обсуждение исходный текст |
Ответы |
Re: TPCH 100GB - need some help
(Rod Taylor <pg@rbt.ca>)
Re: TPCH 100GB - need some help (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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/
В списке pgsql-performance по дате отправления:
Предыдущее
От: "Fabio Panizzutti"Дата:
Сообщение: R: R: R: Query plan on identical tables differs . Why ?