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 920C0D1B1CB for ; Fri, 9 Jul 2004 17:38:50 -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 87533-05 for ; Fri, 9 Jul 2004 20:38:47 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 072E5D1B1B0 for ; Fri, 9 Jul 2004 17:38:44 -0300 (ADT) Received: (qmail 18954 invoked from network); 9 Jul 2004 20:38:43 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 9 Jul 2004 20:38:43 -0000 Subject: Re: Cursors performance (was: Re: [PERFORM] Terrible From: Dave Cramer Reply-To: pg@fastcrypt.com To: Bill Chandler Cc: pgsql-performance@postgresql.org, "pgsql-jdbc@postgresql.org" In-Reply-To: <20040709202416.54013.qmail@web51401.mail.yahoo.com> References: <20040709202416.54013.qmail@web51401.mail.yahoo.com> Content-Type: text/plain Organization: Cramer Consulting Message-Id: <1089405541.3645.289.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Fri, 09 Jul 2004 16:39:01 -0400 Content-Transfer-Encoding: 7bit X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) 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/67 X-Sequence-Number: 10451 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@postgresql.org so that your > message can get through to the mailing list cleanly > > > > !DSPAM:40eefff6170301475214189! > > -- Dave Cramer 519 939 0336 ICQ # 14675561