From: Dror Matalon <dror@zapatec.com>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: Compare rows
Date: Wed, 8 Oct 2003 12:39:37 -0700
Message-ID: <20031008193937.GN2979@rlx11.zapatec.com> (raw)
In-Reply-To: <3F846072.3090700@cranel.com>
References: <3F841A28.8060504@cranel.com>
<200310080901.45093.josh@agliodbs.com>
<3F843AFD.3020700@cranel.com>
<3F843F73.6030302@joeconway.com>
<3F8459FA.1030008@cranel.com>
<20031008185527.GK2979@rlx11.zapatec.com>
<3F846072.3090700@cranel.com>
Greg,
On Wed, Oct 08, 2003 at 03:07:30PM -0400, Greg Spiegelberg wrote:
> Dror,
>
> I gave this some serious thought at first. I only deal with
> int8, numeric(24,12) and varchar(32) columns which I could
> reduce to 3 different tables. Problem was going from 1700-3000
I'm not sure how the data types come into play here. I was for the most
part following your examples.
> rows to around 300,000-1,000,000 rows per system per day that
> is sending data to our database.
>
Depending on the distribution of your data you can end up with more,
less or roughly the same amount of data in the end. It all depends on
how many of the 600+ columns change every time you insert a row. If only
a few of them do, then you'll clearly end up with less total data, since
you'll be writing several rows that are very short instead of one
huge row that contains all the information. In other words, you're
tracking changes better.
It also sounds like you feel that having a few thousand rows in a very
"wide" table is better than having 300,000 - 1,00,000 rows in a "narrow"
table. My gut feeling is that it's the other way around, but there are
plenty of people on this list who can provide a more informed answer.
Using the above eample, assuming that both tables roughly have the same
number of pages in them, would postgres deal better with a table with
3-4 columns with 300,000 - 1,000,000 rows or with a table with several
hundred columns with only 3000 or so rows?
Regards,
Dror
> BTW, the int8 and numeric(24,12) are for future expansion.
> I hate limits.
>
> Greg
>
>
> Dror Matalon wrote:
> >It's still not quite clear what you're trying to do. Many people's gut
> >reaction is that you're doing something strange with so many columns in
> >a table.
> >
> >Using your example, a different approach might be to do this instead:
> >
> > Day | Name | Value
> > ------+-------------+-----------
> > Oct 1 | OS | Solaris 5.8
> > Oct 1 | Patch | 108528-12
> > Oct 3 | Patch | 108528-13
> >
> >
> >You end up with lots more rows, fewer columns, but it might be
> >harder to query the table. On the other hand, queries should run quite
> >fast, since it's a much more "normal" table.
> >
> >But without knowing more, and seeing what the other columns look like,
> >it's hard to tell.
> >
> >Dror
>
>
> --
> Greg Spiegelberg
> Sr. Product Development Engineer
> Cranel, Incorporated.
> Phone: 614.318.4314
> Fax: 614.431.8388
> Email: gspiegelberg@Cranel.com
> Cranel. Technology. Integrity. Focus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: dror@zapatec.com
Subject: Re: Compare rows
In-Reply-To: <20031008193937.GN2979@rlx11.zapatec.com>
References: <3F841A28.8060504@cranel.com> <200310080901.45093.josh@agliodbs.com> <3F843AFD.3020700@cranel.com> <3F843F73.6030302@joeconway.com> <3F8459FA.1030008@cranel.com> <20031008185527.GK2979@rlx11.zapatec.com> <3F846072.3090700@cranel.com> <20031008193937.GN2979@rlx11.zapatec.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