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

Here are the result from "log_duration = true"

DECLARE/1st FETCH: 325014.881 ms
2nd FETCH:         324055.281 ms

--- Dave Cramer <pg@fastcrypt.com> wrote:
> Ok, given that there are 5000 rows, the jdbc driver
> will actually fetch
> all 5000 when you do the fetch, so is it the speed
> of the connection, or
> the actual fetch that is taking the time, again,
> check the server logs
> for duration.
>
> Dave
> On Fri, 2004-07-09 at 17:03, Bill Chandler wrote:
> > 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
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > --
> > > 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
> >
> >
> >
> > !DSPAM:40ef083f256273772718645!
> >
> >
> --
> Dave Cramer
> 519 939 0336
> ICQ # 14675561
>
>




__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail