Fastest way to insert/update many rows

Hi,

I'd like to psycopg2 to fetch a large number of rows (hundreds of millions), perform some computations and put them back into the database.

I can fetch about 130k rows/sec with
cur.execute('select * from stuff')
keyvals = list(cur)
and 100k/sec with
f = io.StringIO()
cur.copy_to(f, 'stuff')
f.seek(0)
keyvals = list(tuple(map(int, l.split('\t'))) for l in f)

but inserting using
cur.executemany('insert into stuff values (%s, %s)', keyvals)
only has a throughput of 23k/sec with ca. 20% CPU used by Python, 80% by Postgres, while
cur.copy_from(io.StringIO('\n'.join('{}\t{}'.format(*r) for r in keyvals)), 'stuff')
manages to insert 1.8M/sec.

I can't quite believe that generating a string should be the fastest method, am I missing something?

What I'd really like to do is
cur.executemany('update stuff set value = %s where key = %s', ...)
but that was orders of magnitude slower still; probably because the order is random, so it performs an index lookup for each key.
Populating a temporary table and using 'update stuff ... from temptable ...' is quicker.

I have to set one column in each row, is there a way to update cursors like in PL/pgSQL's
update <table> set ... where current of <cursor>
i.e. iterate through the rows in the most efficient way for the database.

Or would it be wiser to use PL/Python for this kind of task instead?

--
Pascal Germroth