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 F1C27D1B949 for ; Wed, 8 Oct 2003 16:32:11 +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 95925-05 for ; Wed, 8 Oct 2003 13:31:23 -0300 (ADT) Received: from colsweeper.cranel.com (newmail.cranel.com [12.32.71.147]) by svr1.postgresql.org (Postfix) with ESMTP id 10430D1B8B7 for ; Wed, 8 Oct 2003 13:28:06 -0300 (ADT) Received: from colmail01.cranel.com (colmail01.cranel.com) by colsweeper.cranel.com (Content Technologies SMTPRS 4.2.5) with ESMTP id for ; Wed, 8 Oct 2003 12:30:09 -0400 Received: from cranel.com (gspiegelberg.cranel.com [192.168.11.134]) by colmail01.cranel.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id T6NHFGGX; Wed, 8 Oct 2003 12:30:37 -0400 Message-ID: <3F843AFD.3020700@cranel.com> Date: Wed, 08 Oct 2003 12:27:41 -0400 From: Greg Spiegelberg Organization: Cranel, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: PgSQL Performance ML Subject: Re: Compare rows References: <3F841A28.8060504@cranel.com> <200310080901.45093.josh@agliodbs.com> In-Reply-To: <200310080901.45093.josh@agliodbs.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/216 X-Sequence-Number: 3964 Josh Berkus wrote: > Greg, > > >>Anyone have any suggestions on how to efficiently compare >>rows in the same table? This table has 637 columns to be >>compared and 642 total columns. > > > 637 columns? Are you sure that's normalized? It's hard for me to conceive > of a circumstance where that many columns would be necessary. > > If this isn't a catastrophic normalization problem (which it sounds like), > then you will probably still need to work through procedureal normalization > code, as SQL simply doesn't offer any way around naming all the columns by > hand. Perhaps you could describe the problem in more detail? > 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. The reason for my initial question was this. We save changes only. In other words, if system S has row T1 for day D1 and if on day D2 we have another row T1 (excluding our time column) we don't want to save it. That said, if the 3.4 minutes gets burned during our comparison which saves changes only we may look at reverting to separate tables. There are only 1,700 to 3,000 rows on average per load. Oh, PostgreSQL 7.3.3, PHP 4.3.1, RedHat 7.3, kernel 2.4.20-18.7smp, 2x1.4GHz PIII, 2GB memory, and 1Gbs SAN w/ Hitachi 9910 LUN's. Greg -- Greg Spiegelberg Sr. Product Development Engineer Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: gspiegelberg@Cranel.com Cranel. Technology. Integrity. Focus.