RE : Remote query very slow

From: "Johann Robette" <jrobette(at)onyme(dot)com>
To: "'Oliver Jowett'" <oliver(at)opencloud(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: RE : Remote query very slow
Date: 2004-10-01 07:04:37
Message-ID: 005401c4a784$e9ee80a0$a5010a0a@Johann
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc

Thanks for your answer.
It seems that my problem lie on the not use of Statement.setFetchSize
and the use of the heap.

In fact, it appears that when I select all the fields, it's very slow
but when I select only the PK it's already better. So it seems that the
driver is waiting for the whole data and, only then, returns.

I should investigate in that way.

Last question, I'm using pgAdminIII to perform my queries. I think that
I should have the same problem on the server side than on the remote
machine as it is using the same driver. Am I wrong on that point?

Thanks a lot

-----Message d'origine-----
De : pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] De la part de Oliver Jowett
Envoyé : jeudi 30 septembre 2004 23:55
À : Johann Robette
Cc : pgsql-jdbc(at)postgresql(dot)org
Objet : Re: [JDBC] Remote query very slow

Johann Robette wrote:

> I’ve installed postgres 7.3.5 on a fedora server. It works fine, at
> least on a local basis. I perform an easy select * from a table and I
> get the answer in approximatively 1s.
>
> Now, I used the jdbc driver (jdbc3) for postgres. It connects fine
but,
> performing the same query, I get the answer in 7s.
>
> What could be my problem?

Is it really a "remote query" problem? Or is it just a difference
between using psql and JDBC? How fast is the query if you run it
"remotely" via psql? (psql -h server database)

GC is the most likely culprit if you are running with default heap
settings and a large data set. Without instructions to the contrary
(e.g. Statement.setFetchSize) the driver will try to pull the entire
resultset into heap before returning from query execution. Try turning
on -verbose:gc on your JVM and see how much time is spent doing GC.

You probably want to upgrade both your server and JDBC driver (assuming
you're using the 7.3-era driver) if you can. The 7.3 server is getting
quite old now, and there are a lot of bugfixes in the current driver
that aren't in the 7.3 JDBC driver.

-O

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Oliver Jowett 2004-10-01 07:36:20 Re: RE : Remote query very slow
Previous Message Matthew T. O'Connor 2004-10-01 04:33:28 Re: CREATE INDEX speeds up query on 31 row table ...

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2004-10-01 07:36:20 Re: RE : Remote query very slow
Previous Message Oliver Jowett 2004-09-30 21:55:14 Re: Remote query very slow