Thread: Query Speed!!!

Query Speed!!!

From
Thirumoorthy Bhuvneswari
Date:
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.

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



Re: Query Speed!!!

From
"Sam Liddicott"
Date:

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







Re: Query Speed!!!

From
nconway@klamath.dyndns.org (Neil Conway)
Date:
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



Re: Query Speed!!!

From
Hans-Juergen Schoenig
Date:
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


Re: Query Speed!!!

From
Chris Albertson
Date:
--- 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



Re: Query Speed!!!

From
Thirumoorthy Bhuvneswari
Date:
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

Re: Query Speed!!!

From
Chris Albertson
Date:
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

Re: Query Speed!!!

From
Thirumoorthy Bhuvneswari
Date:
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

Re: Query Speed!!!

From
"Mario Weilguni"
Date:
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!!!


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>