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.
