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 D54C3D1B33F; Fri, 9 Jul 2004 18:55:41 -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 22995-04; Fri, 9 Jul 2004 21:55:42 +0000 (GMT) Received: from yass.opencloud.co.nz (yass.opencloud.co.nz [203.79.85.162]) by svr1.postgresql.org (Postfix) with ESMTP id 9FE5CD1B379; Fri, 9 Jul 2004 18:55:37 -0300 (ADT) Received: from 203-79-104-60.cable.paradise.net.nz ([203.79.104.60] helo=opencloud.com) by yass.opencloud.co.nz with asmtp (Exim 3.35 #1 (Debian)) id 1Bj3LF-00079k-00; Sat, 10 Jul 2004 09:55:37 +1200 Message-ID: <40EF1457.1060002@opencloud.com> Date: Sat, 10 Jul 2004 09:55:35 +1200 From: Oliver Jowett User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Chandler Cc: pgsql-performance@postgresql.org, pgsql-jdbc@postgresql.org Subject: Re: Cursors performance References: <20040709202416.54013.qmail@web51401.mail.yahoo.com> In-Reply-To: <20040709202416.54013.qmail@web51401.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/75 X-Sequence-Number: 10459 Bill Chandler wrote: > 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. I'd check heap size, GC activity (-verbose:gc), CPU use, swapping activity on the *client* side. It may be that your dataset size or physical memory or something similar has changed sufficiently that GC resulting from the data in each 5k row batch is killing you. Can you try a trivial app that runs the same query (with same fetchsize, autocommit, etc) via JDBC and does nothing but steps forward through the resultset, and see how fast it runs? Perhaps the problem is in your processing logic. -O