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:rbt@rbt.ca]
> 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?
>
