From: Rod Taylor <rbt@rbt.ca>
To: Castle, Lindsay <lindsay.castle@eds.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: One table or many tables for data set
Date: 22 Jul 2003 21:50:03 -0400
Message-ID: <1058925003.47745.15.camel@jester> (raw)
In-Reply-To: <B09017B65BC1A54BB0B76202F63DDCCA053248A2@auntm201>
References: <B09017B65BC1A54BB0B76202F63DDCCA053248A2@auntm201>
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?
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: rbt@rbt.ca, lindsay.castle@eds.com
Subject: Re: One table or many tables for data set
In-Reply-To: <1058925003.47745.15.camel@jester>
References: <B09017B65BC1A54BB0B76202F63DDCCA053248A2@auntm201> <1058925003.47745.15.camel@jester>
* 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