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