Re: memory issue for cur.execute

Federico Di Gregorio wrote:
> Il giorno lun, 05/01/2009 alle 11.51 -0600, Randall Smith ha scritto:
>> When issuing a select on a large table, I'm seeing high memory
>> consumption before fetching any records (just during cur.execute).
> 
> It's psycopg default behaviour. It will fetch everything from the
> backend during the execute() call. If you want to use a server-side
> cursor and fetch only a few rows at a time use a named cursor:
> 
> cur = con.cursor("myname")
> cur.execute('select * from my_large_table')
> 
> federico

Alright.  That makes sense.  There's still a large memory (and CPU)
footprint for fetchall.  I'm guessing that's a conversion to Python
types?  Here is a test I did.  I put in a limit to make sure I didn't
swap.  The result was:

time for execute: 9.44527792931
time for fetchall:  5.81065797806
RAM used for execute: 205 MB
RAM used for fetchall: 265 MB
Total RAM used: 470 MB

I tested again, naming the cursor (without fetching results) and it
worked as you said it would, but the rowcount returned -1.  Is there a
way to know how many rows were selected on the server without fetching
them all?

--Randall


Test Below

******

cur = con.cursor()
t1 = time.time()
print commands.getoutput('free')
cur.execute('select * from mybigtable limit 300000')
t2 = time.time()
print commands.getoutput('free')
result = cur.fetchall()
t3 = time.time()
print commands.getoutput('free')
print t2 - t1, t3 - t2


~$ python psycopg2_memtest.py
              total       used       free     shared    buffers     cached
Mem:        999944     479008     520936          0       7192      96120
-/+ buffers/cache:     375696     624248
Swap:      2931820     353004    2578816
              total       used       free     shared    buffers     cached
Mem:        999944     661796     338148          0       7204      96120
-/+ buffers/cache:     558472     441472
Swap:      2931820     353004    2578816
              total       used       free     shared    buffers     cached
Mem:        999944     926416      73528          0       7212      96120
-/+ buffers/cache:     823084     176860
Swap:      2931820     353004    2578816
9.44527792931 5.81065797806