[DB-SIG] Python/PostgreSQL API performance comparison (update)

[Chris Cogdon]

It was suggested to me that PgSQL would perform better if it was 
configured to return a simple list of tuples rather than a PgResultSet 
(which has some cool abilities, like being able to do 
cur.fetchone()['columnname'], for example). So, I've updated my 
performance comparison code ( 
http//onca.catsden.net/~chris/testtimes.py ) to include the new case, 
and the current results are like this:

method [ user time, system time, child user time, child system time, 
real time ]
pg [ 0.120, 0.020, 0.000, 0.000, 29.580 ]
pgdb [ 4.490, 0.010, 0.000, 0.000, 33.250 ]
PgSQL [ 3.650, 0.010, 0.000, 0.000, 41.150 ]
PgSQL (nocursor) [ 3.620, 0.010, 0.000, 0.000, 32.320 ]
PgSQL (nocursor,list) [ 3.550, 0.010, 0.000, 0.000, 32.360 ]
PoPy [ 0.120, 0.000, 0.000, 0.000, 28.990 ]

There is a very very small performance gain by using straight lists in 
pyPgSQL.

Something I forgot to mention last time. The user/system times above 
are for the 'application' process only. Obviously, there is a lot of 
work being dome by the postmaster which is only factored into the above 
times as part of the 'real time' it takes to do the full query.

The performance loss of using DECLARE cursors (shown in the PgSQL 
non-nocursor above) is probably extra work being done by the DMBS 
causing the longer response time. In short, DECLARE cursors probably 
save memory on the application side at the expense of extra work at the 
DMBS. If the application and DMBS reside on the same machine (pretty 
common for web applications), then there's probably no point using 
DECLARE cursors.

Is this kind of thing useful to folk, or would you prefer me shut up 
about it? :)

-- 
    ("`-/")_.-'"``-._        Chris Cogdon <chris at cogdon.org>
     . . `; -._    )-;-,_`)
    (v_,)'  _  )`-.\  ``-'
   _.- _..-_/ / ((.'
((,.-'   ((,/   fL