Re: Performance with very large tables

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
<mailto: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