From: Greg Spiegelberg <gspiegelberg@cranel.com>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: Compare rows
Date: Wed, 08 Oct 2003 12:27:41 -0400
Message-ID: <3F843AFD.3020700@cranel.com> (raw)
In-Reply-To: <200310080901.45093.josh@agliodbs.com>
References: <3F841A28.8060504@cranel.com>
<200310080901.45093.josh@agliodbs.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gspiegelberg@cranel.com
Subject: Re: Compare rows
In-Reply-To: <3F843AFD.3020700@cranel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox