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

Using psql I get the following results:

DECLARE: 37.556 ms
1st FETCH: 553210.220 ms
2nd FETCH: 420.555 ms
3rd FETCH: 433.639 ms
4th FETCH: 421.009 ms
5th FETCH: 436.240 ms

So using psql the 1st fetch is much slower but
all subsequent ones are almost instantaneous.
Whereas using JDBC each fetch takes about 5.5 minutes.

Another curious thing (at least to me): if I turn off
cursors altogether in my JDBC app and just get the
entire result set (about 101000 rows) the whole thing
returns in 68 seconds. This seems counter-intuitive
to me. Why would it take 68 seconds to return
everything but 305 minutes to just return the 1st
5000?

thanks,

Bill
--- Bill Chandler <billybobc1210(at)yahoo(dot)com> wrote:
> 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
> >

__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail