Re: Any advantage to integer vs stored date w. timestamp - Mailing list pgsql-performance

thanks for your reply

> 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?

They are all integer


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

Hmm yeh I could, I think with the amount of data in the db though it behooves me to use one of the date types, even if via lookup table.

So I guess I'm just not sure if I'm really gaining anything by using an integer  date id column and doing a join on a date lookup table, vs just making it a date w. timestamp column and having duplicate dates in that column.

I would imagine internally that the date w. timestamp is stored as perhaps a time_t type  plus some timezone information. I don't know if it takes that much more space, or there's a significant performance penalty in using it

2,500 rows per hour, with duplicate date columns, seems like it could add up though.

thanks

Richard Huxton <dev@archonet.com> wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

---

Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.