Re: memory issue for cur.execute

On Mon, Jan 5, 2009 at 3:13 PM, Randall Smith <[email protected]> wrote:
> When issuing a select on a large table, I'm seeing high memory consumption
> before fetching any records (just during cur.execute).
>
> This simple example on a table with 500,000 records:
>
> *****
>
> con = psycopg2.connect(host=host, database=database, user=user,
>                      password=password)
> cur = con.cursor()
> cur.execute('select * from my_large_table')
>
> *****
>
> consumed 300M and I never fetched a single record.
>
> Python 2.5.2
> pyscopg2 2.0.8 (dec mx dt ext pq3)
> Ubuntu 8.10

This is a detail of how the PostgreSQL client library operates.  When
you perform a query, all the results are fetched by the client.

The named cursor feature Federico mentioned basically gets PostgreSQL
to store this result set on the server side and then issues smaller
queries on fetchone, fetchmany or fetchall to retrieve portions of the
result set (this also explains why the size of the result set is not
available in this case).  Note that the result set still takes up
memory in this case: just on the server side instead.

If you only need a partial result set, it is usually best to express
that requirement in the SQL query via the LIMIT and OFFSET clauses.

James.