Re: One table or many tables for data set

Thanks Joe,

This certainly helps me get going on the right path.

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: Joe Conway [mailto:mail(at)joeconway(dot)com]
Sent: Wednesday, 23 July 2003 11:06 AM
To: Castle, Lindsay
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] One table or many tables for data set

Castle, Lindsay wrote:
> The data structure looks like this:
> element
> date
> num1
> num2
> num3
> num4
> units
>
> There are approx 12,000 distinct elements for a total of about 6 million
> rows of data.

Ahh, that helps! So are the elements evenly distributed, i.e. are there
approx 500 rows of each element? If so, it should be plenty quick to put
all the data in one table with an index on "element" (and maybe a
multicolumn key, depending on other factors).

> The scanning technology I want to use may need a different number of rows
> and different columns depending on the scan formula;
> eg scan1 may need num1, num2 and num3 from the last 200 rows for
> element "x"
> scan2 may need num1, units from the last 10 rows for element "y"

When you say "last X rows", do you mean sorted by "date"? If so, you
might want that index to be on (element, date). Then do:

SELECT num1, num2, num3 FROM mytable WHERE element = 'an_element' order
by date DESC LIMIT 20;

Replace num1, num2, num3 by whatever columns you want, and "LIMIT X" as
the number of rows you want.

HTH,

Joe