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
