X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7A305D1B18A for ; Fri, 14 May 2004 15:12:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 74211-03 for ; Fri, 14 May 2004 15:12:07 -0300 (ADT) Received: from web60608.mail.yahoo.com (web60608.mail.yahoo.com [216.109.119.82]) by svr1.postgresql.org (Postfix) with SMTP id 6E0F1D1B184 for ; Fri, 14 May 2004 15:12:05 -0300 (ADT) Message-ID: <20040514180037.8730.qmail@web60608.mail.yahoo.com> Received: from [200.17.210.82] by web60608.mail.yahoo.com via HTTP; Fri, 14 May 2004 11:00:37 PDT Date: Fri, 14 May 2004 11:00:37 -0700 (PDT) From: Eduardo Almeida Subject: TPCH 100GB - need some help To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/140 X-Sequence-Number: 6971 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/