Re: Performance with very large tables

An old post on the archives might help you -->
http://archives.postgresql.org/pgsql-interfaces/2005-11/msg00010.php that
was an attempt to simulate functionality similar to setFetchSize in JDBC.

---------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 1/15/07, Jan van der Weijde <Jan(dot)van(dot)der(dot)Weijde(at)attachmate(dot)com> wrote:
>
> Unfortunately a large C program has already been written.. But if a
> function like PQsetFetchSize() was available in libpq, that would also solve
> the problem.
>
> ------------------------------
> *From:* Shoaib Mir [mailto:shoaibmir(at)gmail(dot)com]
> *Sent:* Monday, January 15, 2007 13:49
> *To:* Jan van der Weijde
> *Cc:* Alban Hertroys; pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] Performance with very large tables
>
> If you go with Java, you can make it faster by using setFetchSize (JDBC
> functionality) from client and that will help you with the performance in
> case of fetching large amounts of data.
>
> ---------------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
>
> On 1/15/07, Jan van der Weijde < Jan(dot)van(dot)der(dot)Weijde(at)attachmate(dot)com> wrote:
> >
> > That is exactly the problem I think. However I do not deliberately
> > retrieve the entire table. I use the default settings of the PostgreSQL
> > installation and just execute a simple SELECT * FROM table.
> > I am using a separate client and server (both XP in the test
> > environment), but that should not make much difference.
> > I would expect that the default behavior of PostgreSQL should be such
> > that without LIMIT, a SELECT returns records immediately.
> >
> > Thank you,
> > Jan
> >
> > -----Original Message-----
> > From: Alban Hertroys [mailto: alban(at)magproductions(dot)nl]
> > Sent: Monday, January 15, 2007 12:49
> > To: Jan van der Weijde
> > Cc: Richard Huxton; pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] Performance with very large tables
> >
> > Jan van der Weijde wrote:
> > > Thank you.
> > > It is true he want to have the first few record quickly and then
> > > continue with the next records. However without LIMIT it already takes
> >
> >
> > > a very long time before the first record is returned.
> > > I reproduced this with a table with 1.1 million records on an XP
> > > machine and in my case it took about 25 seconds before the select
> > > returned the first record. I tried it both interactively with pgAdmin
> > > and with a C-application using a cursor (with hold). Both took about
> > the same time.
> >
> > Are you sure you don't retrieve the entire result set first, and only
> > start iterating it after that? Notably the fact that LIMIT changes this
> > behaviour seems to point in that direction.
> >
> > A quick calculation shows that (provided my assumption holds true)
> > fetching each record takes about 12.5 usec on average (25s / 2m
> > records). A quick test on our dev-db fetches (~40k records) in 5 usec
> > average, so that looks reasonable to me (apples and oranges, I know).
> >
> > --
> > Alban Hertroys
> > alban(at)magproductions(dot)nl
> >
> > magproductions b.v.
> >
> > T: ++31(0)534346874
> > F: ++31(0)534346876
> > M:
> > I: www.magproductions.nl
> > A: Postbus 416
> > 7500 AK Enschede
> >
> > // Integrate Your World //
> >
> > ---------------------------(end of broadcast)---------------------------
> >
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
>
>