Dustin Sallings wrote:
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])

Reply via email to