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.

Back then it was just explain. explain analyze actually runs the query
and tells you how long each thing too etc... i.e. it gives you the "I
imagine I'll get this many rows back and it'll cost this much" part, then
the cold hard facts of how many rows really came back, and how long it
really too. Quite a nice improvement.

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

Your disk is likely trashing.

Can you set sort_mem on that old version of pgsql to something higher?

set sort_mem = 32768;

or something similar?

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

Yes, the upgrade is very likely to make a difference. The average
performance gain for each version since then has been, in my experience,
anywhere from a few percentage points faster to many times faster,
depending on what you were trying to do.

Why not download 7.4beta5 and see if you can get it to import the data
from 7.1.3? It's close to going production, and in my opinion, 7.4beta5
is probably at least as stable as 7.1.3 considering the number of unfixed
bugs likely to be hiding out there. My guess is that you'll find your
workstation running 74beta5 with one IDE hard drive outrunning your server
with 7.1.3 on it. Seriously.

We're running 7.2.4 where I work, and the change from 7.1 to 7.2 was huge
for us, especially the non-full vacuums.