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 AC230D1B506 for ; Wed, 8 Oct 2003 17:13:08 +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 00359-03 for ; Wed, 8 Oct 2003 14:12:22 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 7D55FD1B4F0 for ; Wed, 8 Oct 2003 14:12:21 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3741468; Wed, 08 Oct 2003 10:12:56 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Greg Spiegelberg , PgSQL Performance ML Subject: Re: Compare rows Date: Wed, 8 Oct 2003 10:10:26 -0700 User-Agent: KMail/1.4.3 References: <3F841A28.8060504@cranel.com> <200310080901.45093.josh@agliodbs.com> <3F843AFD.3020700@cranel.com> In-Reply-To: <3F843AFD.3020700@cranel.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200310081010.26576.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/221 X-Sequence-Number: 3969 Greg, > 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. Hmmm ... if few of those columns are NULL, then you are probably right ... this is probably the most normalized design. If, however, many of columns are NULL the majority of the time, then the design you should be using is a vertial child table, of the form ( value_type | value ). Such a vertical child table would also make your comparison between instances *much* easier, as it could be executed via a simple 4-table-outer-join and 3 where clauses. So even if you don't have a lot of NULLs, you probably want to consider this. > 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. If re-designing the table per the above is not a possibility, then I'd suggest that you locate 3-5 columns that: 1) are not NULL for any row; 2) combined, serve to identify a tiny subset of rows, i.e. 3% or less of the table. Then put a multi-column index on those columns, and do your comparison. Hopefully the planner should pick up on the availablity of the index and scan only the rows retrieved by the index. However, there is the distinct possibility that the presence of 637 WHERE criteria will confuse the planner, causing it to resort to a full table seq scan; in that case, you will want to use a subselect to force the issue. Or, as Joe Conway suggested, you could figure out some kind of value hash that uniquely identifies your rows. -- Josh Berkus Aglio Database Solutions San Francisco