search

chtd / psycopg2cffi

Port to cffi with some speed improvements
Other
177 stars 43 forks source link

bulk_insert() quite faster, but cursor.execute() much slower #58

Open ng-celes opened 8 years ago

ng-celes commented 8 years ago

Hi, I report an weird for me issue. I'm inserting test data into PostgreSQL 9.3 (milions of single, simple rows)

When I'm using bulk_create() method:

_insert_list.append(myModel), every 10000 times myModel.objects.bulk_create(insertlist)

performance raises when switching to PyPy 4.0.1 instead CPython 2.7.6 from 11k inserts/s to 17k inserts/s, which is fine ...

But when I'm using cursor_execute() method:

_querybase = 'INSERT INTO table (id,vg,vn,vb,pt) VALUES ' .... query += '(%i,%i,%i,%i,0)' % (i, 20+i%30, 10+i%20, i%10) .... and every 50000 times cursor.execute(query)

performance significally drops from 35k inserts/s to only ... 8k inserts/s.

Can someone check if this is repeatable issue or anyone has idea why it's happening? The only thing which changes between tests is switching psycopg2 to psycopg2cffi (newest from pip)

lopuhin commented 8 years ago

Small inserts should also be faster, I measured them (indirectly, via Django ORM) here https://github.com/chtd/psycopg2-benchmarks/blob/master/django_bench/bench.py#L51, and here are the results http://chtd.ru/blog/bystraya-rabota-s-postgres-pod-pypy/?lang=en. But there might be something that is indeed slower.