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 BB388329E44 for ; Tue, 14 Sep 2004 07:04:40 +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 03519-05 for ; Tue, 14 Sep 2004 06:04:34 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.195]) by svr1.postgresql.org (Postfix) with ESMTP id 35803329E4C for ; Tue, 14 Sep 2004 07:04:34 +0100 (BST) Received: by mproxy.gmail.com with SMTP id v18so490595rnb for ; Mon, 13 Sep 2004 23:04:33 -0700 (PDT) Received: by 10.38.8.74 with SMTP id 74mr2063333rnh; Mon, 13 Sep 2004 23:04:33 -0700 (PDT) Received: by 10.38.8.4 with HTTP; Mon, 13 Sep 2004 23:04:33 -0700 (PDT) Message-ID: <3f71fdf104091323047de7bb01@mail.gmail.com> Date: Tue, 14 Sep 2004 01:04:33 -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 In-Reply-To: <28432.1095126554@sss.pgh.pa.us> 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> 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/110 X-Sequence-Number: 8238 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? If I get some extra time I'll look into libpq and see what is required to fix the API. Most thirdparty programs and existing JDBC apps won't work with the current paradigm when returning large result sets. Thanks, Stephen On Mon, 13 Sep 2004 21:49:14 -0400, Tom Lane wrote: > Stephen Crowley writes: > > On Mon, 13 Sep 2004 21:11:07 -0400, Tom Lane wrote: > >> Stephen Crowley writes: > >>> Does postgres cache the entire result set before it begins returning > >>> data to the client? > >> > >> The backend doesn't, but libpq does, and I think JDBC does too. > > > That is incredible. Why would libpq do such a thing? > > Because the API it presents doesn't allow for the possibility of query > failure after having given you back a PGresult: either you have the > whole result available with no further worries, or you don't. > If you think it's "incredible", let's see you design an equally > easy-to-use API that doesn't make this assumption. > > (Now having said that, I would have no objection to someone extending > libpq to offer an alternative streaming API for query results. It > hasn't got to the top of anyone's to-do list though ... and I'm > unconvinced that psql could use it if it did exist.)