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 5FB02D1B18C; Mon, 12 Jul 2004 18:05:20 -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 49840-04; Mon, 12 Jul 2004 21:05:13 +0000 (GMT) Received: from NSNOVPS00411.nacio.xythos.com (212-59.84.64.master-link.com [64.84.59.212]) by svr1.postgresql.org (Postfix) with ESMTP id 23039D1B1A0; Mon, 12 Jul 2004 18:05:10 -0300 (ADT) Received: from [192.168.1.69] ([64.154.218.194]) by NSNOVPS00411.nacio.xythos.com with Microsoft SMTPSVC(5.0.2195.6713); Mon, 12 Jul 2004 14:05:11 -0700 Message-ID: <40F2FD08.7020106@xythos.com> Date: Mon, 12 Jul 2004 14:05:12 -0700 From: Barry Lind User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Chandler Cc: Mark Kirkwood , pgsql-jdbc@postgresql.org, pgsql-perform@postgresql.org Subject: Re: [PERFORM] Cursors performance References: <20040712180730.37055.qmail@web51407.mail.yahoo.com> In-Reply-To: <20040712180730.37055.qmail@web51407.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 12 Jul 2004 21:05:11.0407 (UTC) FILETIME=[EB6537F0:01C46853] 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/115 X-Sequence-Number: 10499 Bill, I suspect that this is an artifact of using server side prepared statements. When testing this via psql you will be forming sql like: select ... from ... where ... and real_name like 'NEPOOL%REAL%' ... but the JDBC driver with server side prepared statements is doing: select ... from ... where ... and real_name like ? ... So when the statement is prepared, since it doesn't know what values you are going to use in the bind variable, it will generally take a more concervative execution plan than if it knows what the bind variable is. So I suspect the performance difference is just in the different execution plans for the two different forms of the sql statement. thanks, --Barry Bill Chandler wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org