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

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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] De la part de Oliver Jowett
Envoyé : jeudi 30 septembre 2004 23:55
À : Johann Robette
Cc : pgsql-jdbc@postgresql.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

Johann Robette wrote:

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?

pgAdmin uses libpq for communication with the server, and is written in
C. The JDBC driver implements the client/server protocol itself, and is
written in Java. So they're not really comparable.

-O

Hello,

I'm experiencing a strange problem. Here it is :
I've created a function with a FOR loop.

DECLARE
Current RECORD;
BEGIN
FOR current IN SELECT * FROM employees LOOP
Tmp := current.id;
END LOOP;
...

When I call the function, I get the error :
ERROR: record " current " is unassigned yet

Any idea?

On Fri, 1 Oct 2004 07:24 pm, Johann Robette wrote:

Hello,

I'm experiencing a strange problem. Here it is :
I've created a function with a FOR loop.

DECLARE
Current RECORD;
BEGIN
FOR current IN SELECT * FROM employees LOOP
Tmp := current.id;
END LOOP;
...

current != Current ?

Oh sorry about that!
No, I was alright in the syntax. Both are defined as current.
I can't find where the problem lies...

-----Message d'origine-----
De : pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] De la part de Russell Smith
Envoyé : vendredi 1 octobre 2004 13:20
À : pgsql-hackers@postgresql.org
Objet : Re: [HACKERS] Record unassigned yet

On Fri, 1 Oct 2004 07:24 pm, Johann Robette wrote:

Hello,

I'm experiencing a strange problem. Here it is :
I've created a function with a FOR loop.

DECLARE
Current RECORD;
BEGIN
FOR current IN SELECT * FROM employees LOOP
Tmp := current.id;
END LOOP;
...

current != Current ?

When I call the function, I get the error :
ERROR: record " current " is unassigned yet

Any idea?

---------------------------(end of

broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

Johann Robette wrote:

Oh sorry about that!
No, I was alright in the syntax. Both are defined as current.
I can't find where the problem lies...

The following works fine for me.

=== BEGIN test ===
DROP TABLE employees;

CREATE TABLE employees (id integer, name text);
COPY employees FROM stdin;
1 Aaron Aardvark
2 Betty Bee
3 Carl Cat
\.

CREATE OR REPLACE FUNCTION emp_test_fn() RETURNS boolean AS '
DECLARE
current RECORD;
Tmp integer;
BEGIN
FOR current IN SELECT * FROM employees LOOP
Tmp := current.id;
RAISE NOTICE ''tmp = %'', Tmp;
END LOOP;
RETURN true;
END
'
LANGUAGE 'plpgsql';

SELECT emp_test_fn();
=== END test ===

PS - please reply to the -general list not -hackers.

--
Richard Huxton
Archonet Ltd

"Johann Robette" <jrobette@onyme.com> writes:

I've created a function with a FOR loop.

DECLARE
Current RECORD;
BEGIN
FOR current IN SELECT * FROM employees LOOP
Tmp := current.id;
END LOOP;
...

When I call the function, I get the error :
ERROR: record " current " is unassigned yet

Any idea?

What you showed us looks fine, therefore the problem is in something you
didn't show us.

regards, tom lane

Thanks for your answer.
In fact, I had a syntax error in my function.
Sorry about that!

-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : vendredi 1 octobre 2004 16:47
À : Johann Robette
Cc : pgsql-hackers@postgresql.org
Objet : Re: [HACKERS] Record unassigned yet

"Johann Robette" <jrobette@onyme.com> writes:

I've created a function with a FOR loop.

DECLARE
Current RECORD;
BEGIN
FOR current IN SELECT * FROM employees LOOP
Tmp := current.id;
END LOOP;
...

When I call the function, I get the error :
ERROR: record " current " is unassigned yet

Any idea?

What you showed us looks fine, therefore the problem is in something you
didn't show us.

regards, tom lane