X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C794953407 for ; Thu, 24 Mar 2005 10:10:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52701-09 for ; Thu, 24 Mar 2005 10:10:04 +0000 (GMT) Received: from felix.adpcl.com (felix.adpcl.com [194.98.137.100]) by svr1.postgresql.org (Postfix) with ESMTP id 0B471533FC for ; Thu, 24 Mar 2005 10:10:03 +0000 (GMT) Received: from pcpatrickxp (unknown [150.175.11.30]) by felix.adpcl.com (Postfix) with SMTP id 81BDB7804; Thu, 24 Mar 2005 11:13:08 +0100 (NFT) Message-ID: <007a01c53059$7ca13750$1e0baf96@pcpatrickxp> From: "Patrick Vedrines" To: "Richard Huxton" Cc: "performance pgsql" References: <002601c52f0a$2312a4d0$280baf96@PCPATRICK> <42406A9F.9040404@archonet.com> Subject: Re: CPU 0.1% IOWAIT 99% for decisonnal queries Date: Thu, 24 Mar 2005 11:08:55 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.206 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/500 X-Sequence-Number: 11205 Hello Richard, > Perhaps look into clustering the tables. Good idea : I will try to go further into this way. > > There is no index on the aggregate table since the criterias, their > > number and their scope are freely choosen by the customers. > > Hmm... not convinced this is a good idea. Long days ago, when my application used Informix, I've try to index the aggregate table: It was a nightmare to manage all these indexes (and their volume) for a uncertain benefit. > If you don't have any indexes and the table isn't clustered then PG has > no choice but to scan the entire table for every query. As you note, > that's going to destroy your cache. You can increase the RAM but sooner > or later, you'll get the same problem. I agree with you : You remarks take me not to rely to the cache features. > 3. Split your tables into two - common fields, uncommon fields, that way > filtering on the common fields might take less space. > 4. Split your tables by date, one table per month or year. Then re-write > your customers' queries on-the-fly to select from the right table. Will > only help with queries on date of course. That forces me to rewrite my query generator which is already a very complex program (in fact the heart of the system) > 5. Place each database on its own machine or virtual machine so they > don't interfere with each other. I'm afraid I don't have the money for that. As Simon and Gustavo suggested, I will check my SCSI disks first. Thank a lot for your advises ! Amicalement, Patrick