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.