Ok.. Unless I'm missing something, the data will be static (or near static). It also sounds as if the structure is common for elements, so you probably only want 2 tables.
One with 6 million rows and any row information. The other with 6 million * 12000 rows with the element data linking to the row information line with an identifier, and have an 'element type' (I assume there are 12000 types of elements -- or something of that nature). Unique constraint on (row_identifier, element_type) The speed you achieve will be based on what indexes you create. If you spend most of your time with one or a few (5% or less of the structure) element types, create a partial index for those element types only, and a partial index for all of the others. If you have a standard mathematical operation on num1, num2, etc. you may want to make use of functional indexes to index the result of the calculation. Be sure to create the tables WITHOUT OIDS and be prepared for the dataload to take a while, and CLUSTER the table based on your most commonly used index (once they've been setup). To help with speed, we would need to see EXPLAIN ANALYZE results and the query being performed. On Tue, 2003-07-22 at 21:00, Castle, Lindsay wrote: > All rows have the same structure, the data itself will be different for each > row, the structure is something like this: > > element > date > num1 > num2 > num3 > num4 > units > > Thanks, > > > Lindsay Castle > EDS Australia > Midrange & Distributed Tools > Infrastructure Tools AP > Ph: +61 (0)8 8464 7101 > Fax: +61 (0)8 8464 2135 > > > -----Original Message----- > From: Rod Taylor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 23 July 2003 10:24 AM > To: Castle, Lindsay > Cc: Postgresql Performance > Subject: Re: One table or many tables for data set > > > On Tue, 2003-07-22 at 20:34, Castle, Lindsay wrote: > > Hi all, > > > > I'm working on a project that has a data set of approximately 6million > rows > > with about 12,000 different elements, each element has 7 columns of data. > > Are these 7 columns the same for each element? >
Description: This is a digitally signed message part