Re: slow query performance

Jeff wrote:
> Dave Weaver wrote:
> > 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.
>
> How many rows should that return?
> [explain analyze will tell you that]

"explain analyze" doesn't seem to be part of this postgres version
(or I misunderstood something).
That particular query returned 24 rows.

> and while that runs is your disk thrashing? vmstat's bi/bo columns will
> tell you.

The machine's over the other side of the building, so I can't physically
see if the disk is thrashing.
I'm not sure how to interpret the vmstat output; running "vmstat 1" shows
me bi/bo both at zero (mostly) until I start the query. Then bi shoots up
to around 2500 (bo remains around zero) until the query finishes.

> 7.1 is quite old, but I do understand the pain of migraing to 7.3 [or
> .4beta] with huge db's

Is the upgrade likely to make a difference?
I'm still none-the-wiser wether the problem I have is due to:
1 Postgres version
2 Database size
3 Table structure
4 Configuration issues
5 Slow hardware
6 All of the above
7 None of the above
8 Something else

Thanks for the help,
Dave.