Re: slow query performance

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
>
>
>