The following view creates the illusion of the old ``single-table'' model:
create view samples as select * from samples_1999 union select * from samples_2000 union select * from samples_2001 union select * from samples_2002 union select * from samples_2003 union select * from samples_2004
Try this with UNION ALL (you know there won't be any duplicates) and possibly with some limits too:
SELECT * FROM samples_1999 WHERE ts BETWEEN '1999-01-01 00:00:00+00' AND '1999-12-31 11:59:59+00'
UNION ALL ...
select
s.serial as serial_num,
s.name as name,
date(ts) as day,
min(sample) as min_temp,
avg(sample) as avg_temp,
stddev(sample) as stddev_temp,
max(sample) as max_temp
from
samples inner join sensors s using (sensor_id)
where
ts > current_date - 7
group by
serial_num, name, day
order by
serial_num, day desc
Try restricting the timestamp too
WHERE ts BETWEEN (current_date -7) AND current_timestamp
Hopefully that will give the planner enough smarts to know it can skip most of the sample_200x tables.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
