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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
