Re: slow query performance

Dave Weaver wrote:
  too optimistic?).
>
> For instance:
>     SELECT station, air_temp FROM obs
>          WHERE station = 'EGBB'
>             AND valid_time > '28/8/03 00:00'
>      AND valid_time < '28/10/03 00:00'
>
> takes 4 mins 32 secs.
>
> 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)
>
> A simple "SELECT count(*) from obs" query takes around that sort of time
> too.

That is no test. Postgresql will always fetch the entire table.
>
> I have run "vacuumdb --analyze obs", to little effect.

Check http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html for general
tuning tips and do a vacuum full if table is updated frequently. Also reindex
the indexes after vacuum. It will take quite some time though.

See if that helps.

  Shridhar