X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3B943D1B2BD; Sat, 10 Jul 2004 00:03:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25994-04; Sat, 10 Jul 2004 03:04:03 +0000 (GMT) Received: from linda-1.paradise.net.nz (bm-1a.paradise.net.nz [202.0.58.20]) by svr1.postgresql.org (Postfix) with ESMTP id 433FBD1B269; Sat, 10 Jul 2004 00:03:56 -0300 (ADT) Received: from smtp-2.paradise.net.nz (smtp-2b.paradise.net.nz [202.0.32.211]) by linda-1.paradise.net.nz (Paradise.net.nz) with ESMTP id <0I0M002NO8IOUE@linda-1.paradise.net.nz>; Sat, 10 Jul 2004 15:04:00 +1200 (NZST) Received: from coretech.co.nz (203-96-145-168.adsl.paradise.net.nz [203.96.145.168]) by smtp-2.paradise.net.nz (Postfix) with ESMTP id 2B2219E25B; Sat, 10 Jul 2004 15:04:00 +1200 (NZST) Date: Sat, 10 Jul 2004 15:06:20 +1200 From: Mark Kirkwood Subject: Re: Cursors performance In-reply-to: <20040709202416.54013.qmail@web51401.mail.yahoo.com> To: Bill Chandler Cc: pgsql-performance@postgresql.org, pgsql-jdbc@postgresql.org Message-id: <40EF5D2C.5080506@coretech.co.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.6) Gecko/20040429 References: <20040709202416.54013.qmail@web51401.mail.yahoo.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/82 X-Sequence-Number: 10466 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 > >