From: Richard Huxton <dev@archonet.com>
To: Zoolin Lin <zoolin3g@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Any advantage to integer vs stored date w. timestamp
Date: Wed, 07 Mar 2007 09:05:35 +0000
Message-ID: <45EE805F.1060708@archonet.com> (raw)
In-Reply-To: <20070306214927.30058.qmail@web63311.mail.re1.yahoo.com>
References: <20070306214927.30058.qmail@web63311.mail.re1.yahoo.com>
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
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: dev@archonet.com, zoolin3g@yahoo.com
Subject: Re: Any advantage to integer vs stored date w. timestamp
In-Reply-To: <45EE805F.1060708@archonet.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