Re: Query Speed!!!

> -----Original Message-----
> From: Thirumoorthy Bhuvneswari [mailto:tbhuvneswari@yahoo.com]
> Sent: 08 July 2002 13:24
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Query Speed!!!
>
>
> hi,
> I am using Postgresql-7.1.3 with RedHat Linux-7.1 and
> JDBC. I am having an indexed table with nearly 3,000
> records for a month. My machine is with 256 MB RAM,
> 600 MHz. I am having a report from the abovesaid
> table, which displays 1 month's records at a stretch
> using Swing components. If I run the query from the
> hard-disk
> it takes about 5 seconds to display all the 3,000
> records. If I connect 3 nodes to it and run the query
> from a node, it takes about 1.5 minutes. I don't know
> where I am wrong. Please do reply. thanks.

You might want to get a shell on the box and run:
iostat 1

or something and see how much disk i/o you are getting - a lot I'm guessing.

What you have is disk-head contention, each "node" wants to be reading a
different part of the disk and so most of the time is spent with the disk
moving the head from one part of the disk to the next.

You may also be swapping out if you don't have enough RAM, and this would
make it worse, esp. if the swap device is the same disk as the DB.

Two solutions I know of:
1) Get LOADS of ram so most of the interesting parts of the disk are
bufferred in RAM - less disk contention
We have 8GB RAM for such purposes

2) Use raid.  I'm no great expert on this but if you mirror then you have
two copies and can read from two parts of the disk at the same time, or in
otherwords run 2 nodes without disc contention.
We do this too.

Perhaps for you the real solution is to "explain analyse <your query>" and
look at what it is doing; if you have much seq_scan which could be using an
index then there's something you may want to change; but if you query is
selecting pretty much everything instead of certain records then this won't
help.

Sam