Message-ID: <45EE805F.1060708@archonet.com>
Date: Wed, 07 Mar 2007 09:05:35 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: Zoolin Lin <zoolin3g@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Any advantage to integer vs stored date w. timestamp
References: <20070306214927.30058.qmail@web63311.mail.re1.yahoo.com>
In-Reply-To: <20070306214927.30058.qmail@web63311.mail.re1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Zoolin Lin wrote:
> Hi,
> 
> I have database with a huge amount of data so i'm trying to make it
> as fast as possible and minimize space.
> 
> One thing i've done is join on a prepopulated date lookup table to
> prevent a bunch of rows with duplicate date columns. Without this I'd
> have about 2500 rows per hour with the exact same date w. timestamp
> in them.
> 
> My question is, with postgres do I really gain anything by this, or
> should I just use the date w. timestamp column on the primary table
> and ditch the join on the date_id table.
> 
> Primary table is all integers like:
> 
> date id | num1 | num2 | num3 | num4 | num5 | num6 | num7 | num 8 
> -------------------------------------------------------------------------------------------------
>  primary key is on date to num->6 columns

What types are num1->8?

> date_id lookup table:
> 
> This table is prepopulated with the date values that will be used.
> 
> date_id | date w timestamp ---------------------------------------- 1
> | 2007-2-15 Midnight 2         | 2007-2-15 1 am 3         | 2007-2-15
> 2 am  etc for 24 hours each day

If you only want things accurate to an hour, you could lost the join and
just store it as an int: 2007021500, 2007021501 etc.

That should see you good to year 2100 or so.

-- 
   Richard Huxton
   Archonet Ltd