Message-ID: <21728.24.91.171.78.1160006000.squirrel@mail.mohawksoft.com>
In-Reply-To: <87hcyj64d2.fsf@enterprisedb.com>
References: <306b3b90a0fa1014e09717b77cca886b@biglumber.com>
	<87hcyj64d2.fsf@enterprisedb.com>
Date: Wed, 4 Oct 2006 19:53:20 -0400 (EDT)
Subject: Re: Netflix Prize data
From: "Mark Woodward" <pgsql@mohawksoft.com>
To: "Gregory Stark" <stark@enterprisedb.com>
Cc: "Greg Sabino Mullane" <greg@turnstep.com>, pgsql-hackers@postgresql.org
User-Agent: SquirrelMail/1.4.7
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal

>
> "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.
>

That's good advice, however, It is said that Netflix has greater than 64K
movies, so, while the test info may work with a small int, I doubt the
overall system would work.

The rating, however, is one char 1~9. Would making it a char(1) buy anything?

In wonder....

If I started screwing around with movie ID and rating, and moved them into
one int. One byte for rating, three bytes for movie ID. That could reduce
the data size by at least half gig.