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 D8AF0D1B1CE for ; Mon, 12 Jul 2004 15:07:32 -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 73343-06 for ; Mon, 12 Jul 2004 18:07:32 +0000 (GMT) Received: from web51407.mail.yahoo.com (web51407.mail.yahoo.com [206.190.38.186]) by svr1.postgresql.org (Postfix) with SMTP id B8BE0D1B1C7 for ; Mon, 12 Jul 2004 15:07:29 -0300 (ADT) Message-ID: <20040712180730.37055.qmail@web51407.mail.yahoo.com> Received: from [192.88.67.254] by web51407.mail.yahoo.com via HTTP; Mon, 12 Jul 2004 11:07:29 PDT Date: Mon, 12 Jul 2004 11:07:29 -0700 (PDT) From: Bill Chandler Subject: Re: [PERFORM] Cursors performance To: Mark Kirkwood Cc: pgsql-jdbc@postgresql.org, pgsql-perform@postgresql.org In-Reply-To: <40EF5D2C.5080506@coretech.co.nz> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200407/113 X-Sequence-Number: 10497 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 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