JDBC speed question.

"John Oakes" <john@networkproductions.net> writes:

Anyone have any ideas how to speed up performance using the JDBC driver? A
query that takes me 20 seconds to execute from command line takes 1 full
minute with the jdbc driver. Thanks.

Since you don't guve any details of your query or network setup, it's
hard to help much. What could account for the difference, assuming
that the query is exactly the same in both cases, is that the JDBC
driver connects through TCP/IP (even when on the same host) whereas
'psql' will by default connect through a Unix socket. On most
systems, Unix sockets are considerably faster than even local TCP
sockets. If the JDBC client is running on a different machine, the
difference becomes even more pronounced.

This is assuming that your query is returning a great deal of data, so
the network pipe is a factor in the execution time. If it isn't, then
it's unclear how to help you without more details on your table
layouts and the query itself.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan

Related question: how do you run postgreSQL to simultaneously support a Unix
socket and TCP/IP?

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "John Oakes" <john@networkproductions.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, April 26, 2001 5:35 PM
Subject: Re: [GENERAL] JDBC speed question.

"John Oakes" <john@networkproductions.net> writes:

Anyone have any ideas how to speed up performance using the JDBC driver?

A

"Clayton Vernon" <cvernon@enron.com> writes:

Related question: how do you run postgreSQL to simultaneously support a Unix
socket and TCP/IP?

Just add '-i' to the postmaster startup options. The Unix socket will
still be available.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan

Thanks, but can I specify each port? I've only seen the one documented
command option "-p" which I assume was for the TCP/IP port.

Again, thanks for the help,

Clayton

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "Clayton Vernon" <cvernon@enron.com>
Cc: "John Oakes" <john@networkproductions.net>;
<pgsql-general@postgresql.org>
Sent: Thursday, April 26, 2001 6:19 PM
Subject: Re: [GENERAL] JDBC speed question.

"Clayton Vernon" <cvernon@enron.com> writes:

Related question: how do you run postgreSQL to simultaneously support a

Unix

"Clayton Vernon" <cvernon@enron.com> writes:

Thanks, but can I specify each port? I've only seen the one documented
command option "-p" which I assume was for the TCP/IP port.

Well, Unix sockets don't have a "port". The port number you specify
is appended to the name of the socket in the filesystem (eg
'/tmp/.s.PGSQL.5432') so postmasters running on different ports won't
try to create the same socket.

What exactly are you trying to do?

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan

Hi Clayton,

From what I remember, using -p will change BOTH the TCP/IP port and the
Unix domain socket.

i.e. pg_ctl start -o '-i -p 7777'

Should make PostgreSQL listen on TCP port 7777, and on most unix systems
will create a socket file in /tmp/.s.PGSQL.7777 (and its corresponding
lock file).

Regards and best wishes,

Justin Clift

Clayton Vernon wrote:

Thanks, but can I specify each port? I've only seen the one documented
command option "-p" which I assume was for the TCP/IP port.

Again, thanks for the help,

Clayton

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "Clayton Vernon" <cvernon@enron.com>
Cc: "John Oakes" <john@networkproductions.net>;
<pgsql-general@postgresql.org>
Sent: Thursday, April 26, 2001 6:19 PM
Subject: Re: [GENERAL] JDBC speed question.

"Clayton Vernon" <cvernon@enron.com> writes:

Related question: how do you run postgreSQL to simultaneously support a

Unix

socket and TCP/IP?

Just add '-i' to the postmaster startup options. The Unix socket will
still be available.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

What's the standard methodology in the Postgres world for
creating functions (SQL Server calls them 'stored procedures')
that return more than one column of data per row?

I have tried using the SETOF operand in the CREATE FUNCTION
statement, and that allows me to return more than one row,
but haven't had any luck with specifying more than one return
type and hence more than one column.

Thanks in advance for any assistance,
eliel

"Eliel Mamousette" <eliel@panix.com> writes:

I have tried using the SETOF operand in the CREATE FUNCTION
statement, and that allows me to return more than one row,
but haven't had any luck with specifying more than one return
type and hence more than one column.

You don't specify more than one return type --- you specify one return
type that is a composite type. Composite types are currently tied to
tables; creating a table also creates a type that represents one of its
rows. Thus

create table foo (a int, b int);

create function foobar (...) returns foo as ...

Note that there are some annoying syntactic limitations on what you can
actually *do* with a function returning tuples. We have plans to
improve that situation in 7.2 or beyond, but for now, this facility
isn't nearly as useful as one might think.

regards, tom lane

"Tom Lane" <sss.pgh.pa.us> writes:

"Eliel Mamousette" <eliel@panix.com> writes:

[question re: returning rows deleted to conserve bits]

You don't specify more than one return type --- you specify one return
type that is a composite type. Composite types are currently tied to
tables; creating a table also creates a type that represents one of its
rows. Thus

create table foo (a int, b int, c int);
create function foobar (...) returns foo as ...

Does rule also apply to views?

For example, what is the best practice when one doesn't want to return
a whole row? Given the restriction as stated, if I only wanted to
return column a and c from the table above, would I create a view
fooview and then say that function foobarview returns fooview?

If I write a paragraph about this process, to whom should I mail it for
inclusion in the documentation? I imagine it will be a FAQ for we who
are striving to escape from the legacy of Sybase and Microsoft's SQL
Server....

Note that there are some annoying syntactic limitations on what you can
actually *do* with a function returning tuples. We have plans to
improve that situation in 7.2 or beyond, but for now, this facility
isn't nearly as useful as one might think.

Thanks Tom, but can you be a bit more specific about what one can't do
with returned tuples? It might save some folks (and me) some time.

thanks again, this process has been extremely helpful,
eliel

You just use a view. Period.

CREATE VIEW foobarview AS SELECT a, c FROM foo;

What else do you need?

CREATE VIEW foobarview AS SELECT a*2 AS double, b*c AS multiple,
max(a,c) AS maximum FROM foo;

I believe views does it all.

/Roger

Eliel Mamousette wrote:

We have a table with a lot of user sessions (basically end -time and
length of connection). We would like to query this table to count the max
number of simultaneous sessions, but we are stumped on how to do that.

The only thing I have been able to think of is to iterate over the entire
table and count the number of connections at the beginning and end of each
user session, keeping a variable that records the time and max number of
sessions at each iteration. We can do this in either in Perl or PL/SQL, but
it seems like it would be *horribly* slow, especially considering we have
about 250,000 of these records come in a day.

I also wonder if there might be some intermediate data structure that we
could create to make this easy, but I thought I would rely on other people's
experience before trying to do this myself...:)

Thanks

Is there a way to flush them more often? Otherwise, they can fill up the
disk space and crash the postmaster. I am doing bulk copies, but the logs
hang around for up to 20 minutes after the copy is finished.

Thanks

webb sprague <wsprague@o1.com> writes:

Is there a way to flush them more often?

Reduce the time interval between WAL checkpoints. (But don't overdo it.)
Perhaps every three or so minutes, rather than five?

Otherwise, they can fill up the
disk space and crash the postmaster. I am doing bulk copies, but the logs
hang around for up to 20 minutes after the copy is finished.

Another possibility is to issue a manual CHECKPOINT command as soon as
you've finished a bulk copy.

regards, tom lane