Re: slow query performance

On Thu, 30 Oct 2003, Dave Weaver 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.
>
>
> > 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?

Well, it's likely to get you better help. Explain Analyze (added in 7.2
IIRC) gets us information on the real time spent in operations as well as
the real number of rows.

But, back to the question, what is the definition of the index it's using?
If you don't have already have an index on (station,valid_time does
making one help?