Dave,
Apologies if this has been suggested before, but maybe :
- interchanging the key order for the "obs_pkey" index and
- clustering the "obs" table on "station"
might make these queries go a bit better?
Alternatively if maintaining a cluster on station is infeasable, you
could consider a collection of partial indexes on valid_time for each
station:
create index obs_valid_time _stat1 on obs(valid_time) where station =
'station 1';
(etc for each station)...
regards
Mark
Dave Weaver wrote:
>On the whole, queries are of the form:
>
> SELECT ? FROM obs WHERE station = ?
> AND valid_time < ? AND valid_time > ?
>or:
> SELECT ? FROM obs WHERE station IN (?, ?, ...)
> AND valid_time < ? AND valid_time > ?
>
>An EXPLAIN of the above query says:
> NOTICE: QUERY PLAN:
>
> Index Scan using obs_pkey on obs (cost=0.00..9.01 rows=1 width=20)
>
> Index "obs_pkey"
> Attribute | Type
>------------+--------------------------
> valid_time | timestamp with time zone
> station | character(10)
>unique btree
>
>
>
