From: Hannu Krosing <hannu@tm.ee>
To: Josh Berkus <josh@agliodbs.com>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Compare rows
Date: Thu, 09 Oct 2003 20:16:22 +0300
Message-ID: <1065719782.2529.7.camel@fuji.krosing.net> (raw)
In-Reply-To: <200310082236.59305.josh@agliodbs.com>
References: <3F841A28.8060504@cranel.com>
<200310081611.59179.josh@agliodbs.com>
<m3fzi35fkd.fsf@wolfe.cbbrowne.com>
<200310082236.59305.josh@agliodbs.com>
Josh Berkus kirjutas N, 09.10.2003 kell 08:36:
> Chris,
> > The need to do a lot of joins would likely hurt performance somewhat,
> > as well as the way that it greatly increases the number of rows.
> > Although you could always split it into several tables, one for each
> > "value_type", and UNION them into a view...
>
> It increases the number of rows, yes, but *decreases* the storage size of data
> by eliminating thousands ... or millions ... of NULL fields.
I'm not sure I buy that.
Null fields take exactly 1 *bit* to store (or more exactly, if you have
any null fields in tuple then one 32bit int for each 32 fields is used
for NULL bitmap), whereas the same fields in "vertical" table takes 4
bytes for primary key and 1-4 bytes for category key + tuple header per
value + neccessary indexes. So if you have more than one non-null field
per tuple you will certainly lose in storage.
> How would splitting the vertical values into dozens of seperate tables help things?
If you put each category in a separate table you save 1-4 bytes for
category per value, but still store primary key and tuple header *per
value*.
Jou may stii get better performance for single-column comparisons as
fewer pages must be touched.
> Personally, I'd rather have a table with 3 columns and 8 million rows than a
> table with 642 columns and 100,000 rows. Much easier to deal with.
Same here ;)
------------------
Hannu
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: hannu@tm.ee, josh@agliodbs.com, cbbrowne@acm.org
Subject: Re: Compare rows
In-Reply-To: <1065719782.2529.7.camel@fuji.krosing.net>
* 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