X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id F3799D1B506 for ; Wed, 8 Oct 2003 16:38:57 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 96807-06 for ; Wed, 8 Oct 2003 13:38:11 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id CD8A6D1B8AE for ; Wed, 8 Oct 2003 13:37:48 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h98GgcwK015248 for ; Wed, 8 Oct 2003 22:12:38 +0530 Received: from persistent.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h98GgbNs015222; Wed, 8 Oct 2003 22:12:37 +0530 Message-ID: <3F843D59.3060204@persistent.co.in> Date: Wed, 08 Oct 2003 22:07:45 +0530 From: Shridhar Daithankar Organization: Persistent Systems Pvt. Ltd. User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5b) Gecko/20030917 Thunderbird/0.3a X-Accept-Language: en-us, en MIME-Version: 1.0 To: Greg Spiegelberg Cc: PgSQL Performance ML Subject: Re: Compare rows References: <3F841A28.8060504@cranel.com> <200310080901.45093.josh@agliodbs.com> <3F843AFD.3020700@cranel.com> In-Reply-To: <3F843AFD.3020700@cranel.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/217 X-Sequence-Number: 3965 Greg Spiegelberg wrote: > The data represents metrics at a point in time on a system for > network, disk, memory, bus, controller, and so-on. Rx, Tx, errors, > speed, and whatever else can be gathered. > > We arrived at this one 642 column table after testing the whole > process from data gathering, methods of temporarily storing then > loading to the database. Initially, 37+ tables were in use but > the one big-un has saved us over 3.4 minutes. I am sure you changed the desing because those 3.4 minutes were significant to you. But I suggest you go back to 37 table design and see where bottleneck is. Probably you can tune a join across 37 tables much better than optimizing a difference between two 637 column rows. Besides such a large number of columns will cost heavily in terms of defragmentation across pages. The wasted space and IO therof could be significant issue for large number of rows. 642 column is a bad design. Theoretically and from implementation of postgresql point of view. You did it because of speed problem. Now if we can resolve those speed problems, perhaps you could go back to other design. Is it feasible for you right now or you are too much committed to the big table? And of course, then it is routing postgresql tuning exercise..:-) Shridhar