pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Chandler <billybobc1210@yahoo.com>
To: pg@fastcrypt.com
Cc: pgsql-jdbc@postgresql.org
Subject: Re: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
Date: Fri, 9 Jul 2004 14:03:48 -0700 (PDT)
Message-ID: <20040709210348.1813.qmail@web51406.mail.yahoo.com> (raw)
In-Reply-To: <1089405541.3645.289.camel@localhost.localdomain>

Using psql it peforms exactly as I'd expect.  The
rows get printed out to stdout, I hold down the space
bar to keep everything scrolling and as every 5000
rows go by I see a new fetch statement logged in the
server log.  The results from this statement seem to
come back instaneously and the output starts scrolling
again immediately.  Whole query takes a few minutes 
to complete.

I seems like it has something to do w/ my JDBC app
but I can't think for the life of me what I might have
changed.  Anyway, there's only the setFetchSize(5000)
and the setAutoCommit(false) that are relevant to
cursors, right?  And those have been in there for 
weeks.

Bill

--- Dave Cramer <pg@fastcrypt.com> wrote:
> Bill,
> 
> What happens if you do this in psql, also you can
> turn on duration
> logging in the backend and log the queries.
> 
> dave
> On Fri, 2004-07-09 at 16:24, Bill Chandler wrote:
> > 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
> > 
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> send an appropriate
> >       subscribe-nomail command to
> majordomo@postgresql.org so that your
> >       message can get through to the mailing list
> cleanly
> > 
> > 
> > 
> > !DSPAM:40eefff6170301475214189!
> > 
> > 
> -- 
> Dave Cramer
> 519 939 0336
> ICQ # 14675561
> 
> 


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



view thread (6+ messages)  latest in thread

Message-ID: <20040709210348.1813.qmail@web51406.mail.yahoo.com>
Permalink:  ../20040709210348.1813.qmail@web51406.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040709210348.1813.qmail@web51406.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: billybobc1210@yahoo.com, pg@fastcrypt.com, pgsql-jdbc@postgresql.org
  Subject: Re: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
  In-Reply-To: <20040709210348.1813.qmail@web51406.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox