Re: [PERFORM] Cursors performance

Thanks,

Will try this test (I'm assuming you mean to say to
set fetch size of 1 and rerun on both JDBC and
psql).

BTW, here is another clue: I only get the JDBC
performance degradation when I include the "real_name
like 'NEPOOL%REAL%'" clause. I've tried re-ordering
too: i.e. putting this clause first in the statement,
last in the statement, etc. Doesn't seem to make any
difference.

real_name is a varchar(64). There is a unique index
on it.

thanks,

Bill

--- Mark Kirkwood <markir(at)coretech(dot)co(dot)nz> wrote:
> Might be worth doing a little test:
>
> i) modify your code to fetch 1 row at a time
> ii) set log_duration=true in your postgresql.conf
> (as the other posters
> have suggested)
>
> Then compare with running the query in psql.
>
> regards
>
> Mark
>
>
>
> 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 8: explain analyze is your friend
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail