Re: inconsistent/weird index usage

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