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

Thirumoorthy Bhuvneswari wrote:

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.

bhuvana.

Did you define indexes? did you vacuum the database?
if that doesn't work try to give the optimizer some hints (see manual).

HAns

On Mon, Jul 08, 2002 at 05:24:28AM -0700, Thirumoorthy Bhuvneswari wrote:

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.

How many months are you storing? i.e. how large is the
entire table?

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.

Not sure what you mean by "running it from the hard disk",
versus "connecting nodes". If you mean that the query takes
longer when executed by clients on a LAN, sounds like a
network problem.

If not, then you need to provide a LOT more information. As is,
we can't help you. Have you VACUUM ANALYZE'd the tables in
question? How much data (# of rows) is involved? What queries
are being executed? What does EXPLAIN show for these queries?
What is the definition of the tables involved, as well as
any applicable indexes? Under what exact circumstances does
the performance problem occur?

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

--- Hans-Juergen Schoenig <hs@cybertec.at> wrote:

Thirumoorthy Bhuvneswari wrote:

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.

Please tell us two more things:

1) If you enter the query by hand at the psql prompt how
long does the query take?

2) What is the SQL query? Show us the SQL you entered.

I would expect much beter 10x to 100x speeds for most "normal"
queries. But do the above _after_ a "vacuum full analyze"

=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

hi,
my query looks like this:

select * from loom_details where date>='2002-06-01'
and date<='2002-06-30'.

* I do give 'select *' since all the columns are
required for the report.

* When I give the above query in the 'psql' prompt it
waits for 1 second to fetch the records and displays
the entire rows(about 3000).

* There are about 10,000 records total in the table.

* When I run the report, it actually does not increase
the RAM usage but takes about 90% of the CPU time
leaving all the other queries behind it.

Please tell me how can I increase the speed. thanks.

regards,
T.Bhuvaneswari.

--- Chris Albertson <chrisalbertson90278@yahoo.com>
wrote:

--- Hans-Juergen Schoenig <hs@cybertec.at> wrote:

Thirumoorthy Bhuvneswari wrote:

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.

Please tell us two more things:

1) If you enter the query by hand at the psql prompt
how
long does the query take?

2) What is the SQL query? Show us the SQL you
entered.

I would expect much beter 10x to 100x speeds for
most "normal"
queries. But do the above _after_ a "vacuum full
analyze"

=====
Chris Albertson
Home: 310-376-1029
chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189
Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

Try this then re-run the querry.

CREATE INDEX date_indx ON loom_details (date);
VACUUM FULL ANALYZE;

I don't like using "SELECT *" in code because if later I
add a new column I have to go back and fix all those querries.
And also the querry is hard to read later with out looking up
the table's structure. But clearly "select * works and I use
i at the psql prompt all the time.

--- Thirumoorthy Bhuvneswari <tbhuvneswari@yahoo.com> wrote:

hi,
my query looks like this:

select * from loom_details where date>='2002-06-01'
and date<='2002-06-30'.

* I do give 'select *' since all the columns are
required for the report.

* When I give the above query in the 'psql' prompt it
waits for 1 second to fetch the records and displays
the entire rows(about 3000).

* There are about 10,000 records total in the table.

* When I run the report, it actually does not increase
the RAM usage but takes about 90% of the CPU time
leaving all the other queries behind it.

Please tell me how can I increase the speed. thanks.

regards,
T.Bhuvaneswari.

--- Chris Albertson <chrisalbertson90278@yahoo.com>
wrote:

--- Hans-Juergen Schoenig <hs@cybertec.at> wrote:

Thirumoorthy Bhuvneswari wrote:

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.

Please tell us two more things:

1) If you enter the query by hand at the psql prompt
how
long does the query take?

2) What is the SQL query? Show us the SQL you
entered.

I would expect much beter 10x to 100x speeds for
most "normal"
queries. But do the above _after_ a "vacuum full
analyze"

=====
Chris Albertson
Home: 310-376-1029
chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189
Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

hi Chris,
Thanks for your idea. But, if I give the command
'VACUUM FULL ANALYZE', it says a parsing error.
Actually, I use to give

'vacuumdb databasename' from outside the psql prompt
weekly. Whether it is 'VACUUM FULL ANALYZE' or I use
to give 'VACUUM ANALYZE tablename' it is the one.
Please let me know. In 'man vacuum' it just says
'VACUUM VERBOSE ANALYZE' and not 'VACUUM FULL
ANALYZE'. Please
forgive if I am wrong somewhere. thanks.

regards,
bhuvana.

--- Chris Albertson <chrisalbertson90278@yahoo.com>
wrote:

--- Thirumoorthy Bhuvneswari
<tbhuvneswari@yahoo.com> wrote:

<SNIP>

....Also, what is 'VACUUM FULL ANALYZE'.

THat's it. If you don't know that you must not have
done it. Do it and things will speed up. Run it as
a nightly cron job and it will stay fast.

See "man vacuum"

It is a SQL statment that compacts the tables and
populates
statics about the table that the querry optimizer an
use.

=====
Chris Albertson
Home: 310-376-1029
chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189
Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

vacuum full is supported in 7.2.x, not any prior release.

----- Original Message -----
From: "Thirumoorthy Bhuvneswari" <tbhuvneswari@yahoo.com>
To: "Chris Albertson" <chrisalbertson90278@yahoo.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, July 12, 2002 10:54 AM
Subject: Re: [GENERAL] Query Speed!!!