[pypy-dev] PyPQ: a dbapi 2 PostgreSQL driver working with pypy

[Igor Katson]

So I've launched a few tests for all PostgreSQL python drivers
out there, and optimized PyPq a bit along the way.

*IntergerInsert* test does this 30000 times (i wanted more, but pg8000 
bloats
postgres memory usage to 1,5 gigabytes on this somewhy, so i lowered the 
amount
of queries to 30000)

     cursor.executemany('insert into test_table values(%s, %s, %s, %s)', 
[(1,2,3,4)] * 30000)

*IntegerSelect* selects this data back into python

*VariableDataInsert* does the same as IntegerInsert, but inserts a string, a
datetime, a date and a timestamp into the database (except for pg8000, 
which told
me that it did not support timestamps)

*VariableDataSelect* selects this data back into python

cPython 2.7.2 (32-bit, archlinux latest build), 30000 inserts

Psycopg2IntegerInsert.test_insert took 1.78s
.Psycopg2IntegerSelect.test_select took 0.06s
.Psycopg2VariableDataInsert.test_insert took 2.57s
.Psycopg2VariableDataSelect.test_select took 0.25s

.Psycopg2ctIntegerInsert.test_insert took 4.46s
.Psycopg2ctIntegerSelect.test_select took 1.62s
.Psycopg2ctVariableDataInsert.test_insert took 6.00s
.Psycopg2ctVariableDataSelect.test_select took 3.31s

.PyPQIntegerInsertTest.test_insert took 3.41s
.PyPQIntegerSelectTest.test_select took 0.84s
.PyPQVariableDataInsertTest.test_insert took 4.07s
.PyPQVariableDataSelectTest.test_select took 3.70s

pg8000IntegerInsert.test_insert took 16.20s
.pg8000IntegerSelect.test_select took 1.43s
.pg8000VariableDataInsert.test_insert took 18.00s
.pg8000VariableDataSelect.test_select took 2.17s

PyPy 1.6.0 (32-bit, archlinux latest build), 30000 inserts

Psycopg2ctIntegerInsert.test_insert took 2.69s
.Psycopg2ctIntegerSelect.test_select took 0.63s
.Psycopg2ctVariableDataInsert.test_insert took 4.53s
.Psycopg2ctVariableDataSelect.test_select took 1.36s

.PyPQIntegerInsertTest.test_insert took 4.61s
.PyPQIntegerSelectTest.test_select took 0.37s
.PyPQVariableDataInsertTest.test_insert took 4.48s
.PyPQVariableDataSelectTest.test_select took 1.58s

pg8000IntegerInsert.test_insert took 8.34s
.pg8000IntegerSelect.test_select took 0.60s
.pg8000VariableDataInsert.test_insert took 9.15s
.pg8000VariableDataSelect.test_select took 1.64s

As we can see, pg8000 is slow on inserts, and as i've said, it does some 
strange
things to my postgres, bloating the postgres memory usage to 1.5 
gigabytes (i tried
to insert 100000 records with executemany)

On cPython, pypq is faster than psycopg2ct and pg8000, except for 
VariableDataSelect
test.
On PyPy, all of them get faster, except pypq, though it is still a bit 
faster
than psycopg2ct in 2 tests.

Next, I tested pypq side by side to see the difference more clearly.

Here are the results.

cPython 2.7.2 (32-bit, archlinux latest build), 200000 inserts

Psycopg2IntegerInsert.test_insert took 12.22s
.Psycopg2IntegerSelect.test_select took 0.39s
.Psycopg2VariableDataInsert.test_insert took 17.30s
.Psycopg2VariableDataSelect.test_select took 1.71s

.Psycopg2ctIntegerInsert.test_insert took 28.56s
.Psycopg2ctIntegerSelect.test_select took 10.48s
.Psycopg2ctVariableDataInsert.test_insert took 38.53s
.Psycopg2ctVariableDataSelect.test_select took 21.67s

.PyPQIntegerInsertTest.test_insert took 22.53s
.PyPQIntegerSelectTest.test_select took 5.59s
.PyPQVariableDataInsertTest.test_insert took 26.86s
.PyPQVariableDataSelectTest.test_select took 24.84s

PyPy 1.6.0 (32-bit, archlinux latest build), 200000 inserts

Psycopg2ctIntegerInsert.test_insert took 14.11s
.Psycopg2ctIntegerSelect.test_select took 3.18s
.Psycopg2ctVariableDataInsert.test_insert took 29.36s
.Psycopg2ctVariableDataSelect.test_select took 7.78s

.PyPQIntegerInsertTest.test_insert took 25.91s
.PyPQIntegerSelectTest.test_select took 1.92s
.PyPQVariableDataInsertTest.test_insert took 30.31s
.PyPQVariableDataSelectTest.test_select took 8.73s

On 10/05/2011 01:38 AM, Maciej Fijalkowski wrote:
> On Tue, Oct 4, 2011 at 4:51 PM, Igor Katson<igor.katson at gmail.com>  wrote:
>> Hi, Dan,
>> before answering I'll describe the situation a bit.
>>
>> there was a question today, if I know about pg8000 or psycopg2ct.
>>
>> As for pg8000, pure python should be slower than ctypes, anyway, so I don't
>> think these two should be compared.
> [citation needed]