Lists: | pgsql-hackerspgsql-jdbc |
---|
From: | "Johann Robette" <jrobette(at)onyme(dot)com> |
---|---|
To: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Remote query very slow |
Date: | 2004-09-30 09:50:25 |
Message-ID: | 003101c4a6d2$ec1971d0$a5010a0a@Johann |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
Hi,
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?
Thanks in advance.
From: | Oliver Jowett <oliver(at)opencloud(dot)com> |
---|---|
To: | Johann Robette <jrobette(at)onyme(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Remote query very slow |
Date: | 2004-09-30 21:55:14 |
Message-ID: | 415C80C2.4010404@opencloud.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
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
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 |
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:
> Ive 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
From: | Oliver Jowett <oliver(at)opencloud(dot)com> |
---|---|
To: | Johann Robette <jrobette(at)onyme(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: RE : Remote query very slow |
Date: | 2004-10-01 07:36:20 |
Message-ID: | 415D08F4.7010705@opencloud.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
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
From: | "Johann Robette" <jrobette(at)onyme(dot)com> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Record unassigned yet |
Date: | 2004-10-01 09:24:39 |
Message-ID: | 005801c4a798$7ce9c820$a5010a0a@Johann |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
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?
From: | Russell Smith <mr-russ(at)pws(dot)com(dot)au> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Record unassigned yet |
Date: | 2004-10-01 11:19:51 |
Message-ID: | 200410012119.51996.mr-russ@pws.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
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
>
>
From: | "Johann Robette" <jrobette(at)onyme(dot)com> |
---|---|
To: | "'Russell Smith'" <mr-russ(at)pws(dot)com(dot)au>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE : Record unassigned yet |
Date: | 2004-10-01 11:28:33 |
Message-ID: | 005f01c4a7a9$c8b80030$a5010a0a@Johann |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
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(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] De la part de Russell Smith
Envoyé : vendredi 1 octobre 2004 13:20
À : pgsql-hackers(at)postgresql(dot)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
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Johann Robette <jrobette(at)onyme(dot)com> |
Cc: | 'Russell Smith' <mr-russ(at)pws(dot)com(dot)au>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: RE : Record unassigned yet |
Date: | 2004-10-01 11:45:54 |
Message-ID: | 415D4372.8090303@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Johann Robette" <jrobette(at)onyme(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Record unassigned yet |
Date: | 2004-10-01 14:47:05 |
Message-ID: | 9449.1096642025@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
"Johann Robette" <jrobette(at)onyme(dot)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
From: | "Johann Robette" <jrobette(at)onyme(dot)com> |
---|---|
To: | "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE : Record unassigned yet |
Date: | 2004-10-01 14:51:14 |
Message-ID: | 006a01c4a7c6$1c6f0ea0$a5010a0a@Johann |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers pgsql-jdbc |
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(at)sss(dot)pgh(dot)pa(dot)us]
Envoyé : vendredi 1 octobre 2004 16:47
À : Johann Robette
Cc : pgsql-hackers(at)postgresql(dot)org
Objet : Re: [HACKERS] Record unassigned yet
"Johann Robette" <jrobette(at)onyme(dot)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