X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7FE4D329E2C for ; Fri, 24 Sep 2004 00:37:01 +0100 (BST) 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 23736-07 for ; Thu, 23 Sep 2004 23:36:49 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.197]) by svr1.postgresql.org (Postfix) with ESMTP id 645F2329E12 for ; Fri, 24 Sep 2004 00:36:50 +0100 (BST) Received: by mproxy.gmail.com with SMTP id v30so92907rnb for ; Thu, 23 Sep 2004 16:36:49 -0700 (PDT) Received: by 10.38.179.69 with SMTP id b69mr255241rnf; Thu, 23 Sep 2004 16:36:49 -0700 (PDT) Received: by 10.38.8.71 with HTTP; Thu, 23 Sep 2004 16:36:49 -0700 (PDT) Message-ID: <3f71fdf104092316367c5f3052@mail.gmail.com> Date: Thu, 23 Sep 2004 18:36:49 -0500 From: Stephen Crowley Reply-To: Stephen Crowley To: pgsql-performance@postgresql.org Subject: Re: Large # of rows in query extremely slow, not using index Cc: Kris Jurka In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <3f71fdf104091317515c756512@mail.gmail.com> <28088.1095124267@sss.pgh.pa.us> <3f71fdf104091318228099815@mail.gmail.com> <28432.1095126554@sss.pgh.pa.us> <3f71fdf104091323047de7bb01@mail.gmail.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: 200409/260 X-Sequence-Number: 8388 Thanks for the explanation. So what sort of changes need to be made to the client/server protocol to fix this problem? On Thu, 23 Sep 2004 18:22:15 -0500 (EST), Kris Jurka wrote: > > > On Tue, 14 Sep 2004, Stephen Crowley wrote: > > > Problem solved.. I set the fetchSize to a reasonable value instead of > > the default of unlimited in the PreparedStatement and now the query > > is . After some searching it seeems this is a common problem, would it > > make sense to change the default value to something other than 0 in > > the JDBC driver? > > In the JDBC driver, setting the fetch size to a non-zero value means that > the query will be run using what the frontend/backend protocol calls a > named statement. What this means on the backend is that the planner will > not be able to use the values from the query parameters to generate the > optimum query plan and must use generic placeholders and create a generic > plan. For this reason we have decided not to default to a non-zero > fetch size. This is something whose default value could be set by a URL > parameter if you think that is something that is really required. > > Kris Jurka > >