Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)

From: Bill Chandler <billybobc1210(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
Date: 2004-07-09 20:24:16
Message-ID: 20040709202416.54013.qmail@web51401.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc pgsql-performance

Thanks to all who have responded. I now think my
problem is not related to deleting/recreating indexes.
Somehow it is related to JDBC cursors. It appears
that what is happening is that since I'm using
a fetch size of 5000, the command:

FETCH FORWARD 5000 FROM JDBC_CURS_1

is being repeatedly sent to the server as I process
the result set from my query. Each time this command
is sent it it takes about 5 minutes to return which is
about the amount of time the whole query took to
complete before the performance degredation. So in
other words it looks as if the full select is being
rerun on each fetch.

Now the mystery is why is this happening all of the
sudden? I have been running w/ fetch size set to 5000
for the last couple of weeks and it did not appear to
be doing this (i.e. re-running the entire select
statement again). Is this what I should expect when
using cursors? I would have thought that the server
should "remember" where it left off in the query since
the last fetch and continue from there.

Could I have inadvertently changed a parameter
somewhere that would cause this behavior?

thanks,

Bill

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2004-07-09 20:39:01 Re: Cursors performance (was: Re: [PERFORM] Terrible
Previous Message Rein Reezigt 2004-07-09 16:03:22 unsuscribe

Browse pgsql-performance by date

  From Date Subject
Next Message Dave Cramer 2004-07-09 20:39:01 Re: Cursors performance (was: Re: [PERFORM] Terrible
Previous Message Rod Taylor 2004-07-09 19:18:51 Re: query plan wierdness?