From: Gregory Stark <stark@enterprisedb.com>
To: "Greg Sabino Mullane" <greg@turnstep.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Netflix Prize data
In-Reply-To: <306b3b90a0fa1014e09717b77cca886b@biglumber.com> (Greg Sabino
	Mullane's message of "Wed, 4 Oct 2006 22:34:52 -0000")
Organization: EnterpriseDB
References: <306b3b90a0fa1014e09717b77cca886b@biglumber.com>
Date: Wed, 04 Oct 2006 19:36:09 -0400
Message-ID: <87hcyj64d2.fsf@enterprisedb.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/22.0.50 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"Greg Sabino Mullane" <greg@turnstep.com> writes:

> CREATE TABLE rating (
>   movie  SMALLINT NOT NULL,
>   person INTEGER  NOT NULL,
>   rating SMALLINT NOT NULL,
>   viewed DATE     NOT NULL
> );

You would probably be better off putting the two smallints first followed by
the integer and date. Otherwise both the integer and the date field will have
an extra two bytes of padding wasting 4 bytes of space.

If you reorder the fields that way you'll be down to 28 bytes of tuple header
overhead and 12 bytes of data. There's actually another 4 bytes in the form of
the line pointer so a total of 44 bytes per record. Ie, almost 73% of the disk
i/o you're seeing is actually per-record overhead.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com