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

I should have said the results below are from
running the query via JDBC. I'll get some
results from psql and post those as well.

--- Bill Chandler <billybobc1210(at)yahoo(dot)com> wrote:
> Here are the result from "log_duration = true"
>
> DECLARE/1st FETCH: 325014.881 ms
> 2nd FETCH: 324055.281 ms
>
> --- Dave Cramer <pg(at)fastcrypt(dot)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(at)fastcrypt(dot)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(at)postgresql(dot)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
>

__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail