pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephen Crowley <stephen.crowley@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Large # of rows in query extremely slow, not using index
Date: Tue, 14 Sep 2004 01:04:33 -0500
Message-ID: <3f71fdf104091323047de7bb01@mail.gmail.com> (raw)
In-Reply-To: <28432.1095126554@sss.pgh.pa.us>
References: <3f71fdf104091317515c756512@mail.gmail.com>
	<28088.1095124267@sss.pgh.pa.us>
	<3f71fdf104091318228099815@mail.gmail.com>
	<28432.1095126554@sss.pgh.pa.us>

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 <tgl@sss.pgh.pa.us> wrote:
> Stephen Crowley <stephen.crowley@gmail.com> writes:
> > On Mon, 13 Sep 2004 21:11:07 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Stephen Crowley <stephen.crowley@gmail.com> 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.)



view thread (17+ messages)  latest in thread

Message-ID: <3f71fdf104091323047de7bb01@mail.gmail.com>
Permalink:  ../3f71fdf104091323047de7bb01@mail.gmail.com/
Also on:    postgresql.org/message-id/3f71fdf104091323047de7bb01@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: stephen.crowley@gmail.com
  Subject: Re: Large # of rows in query extremely slow, not using index
  In-Reply-To: <3f71fdf104091323047de7bb01@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox