Re: slow query performance

On Thu, 30 Oct 2003 13:49:46 -0000
"Dave Weaver" <davew@wsieurope.com> wrote:

> 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.
>
You run explain analyze [insert query here]

Post that output


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

The BI column means it is reading 2500 blocks / second. This is
typically kB/sec (Linux defaults to 1kB block size on filesystems)

That seems pretty low.. even for an older disk.
We'll need the explain analyze output to help further.


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/