Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-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

pgsql-performance by date

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

pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group